package com.ivo.biz;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;
import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;

import com.ivo.core.hibernate.HibernateInvoker;
import com.ivo.core.hibernate.HqlHelper;
import com.ivo.sec.SessionLocal;
import com.ivo.sys.Code;
import com.ivo.sys.user.Role;
import com.ivo.sys.user.User;
import com.ivo.sys.user.UserManager;
import com.ivo.sys.user.UserRole;
import com.ivo.sys.user.UserRoleManager;
import com.ivo.sys.user.UserService;
import com.ivo.util.DBUtil;
import com.ivo.util.DateUtil;
import com.ivo.util.PMSDBUtil;
import com.ivo.util.StringUtil;

/**
 * 
 *
 * @author Minkey Wang
 * @version 2017年7月3日
 */
@Service
public class PerformanceService {
    @Autowired
    private HibernateInvoker hibernateInvoker;
    
    @Autowired
    private PerformanceMonManager performanceMonManager;
    
    @Autowired
    private PerformanceManager performanceManager;
    
    @Autowired
    private PerformanceDayManager performanceDayManager;
    
	@Autowired
	private OperationForemanManager operationForemanManager;
    
	@Autowired
    private PermissionsManager permissionsManager;
	
    @Autowired
    private DustfreeManager dustfreeManager;
    
    @Autowired
    private MajorAdditionManager majorAdditionManager;
	
    @Autowired
    private UserManager userManager;
    
    @Autowired
    private UserRoleManager userRoleManager;
    
	@Autowired
	private UserService userService;
	
    public List list(int allFlag,int pno,String emp_id,String month,Double bonus,Double score,Long[] ope_fk, String classType,String condition){
    	
        String user_id="sys";
        if(SessionLocal.getUser()!=null)
        user_id = SessionLocal.getUser().getUser_id();
        Role role = userService.getRoleByUser(user_id);
        StringBuilder hql = new StringBuilder();
        
        if(role.getName().equals("FORE")){//领班
            hql =hql.append("select new map(d.id as id, d.emp_id as emp_id, d.emp_name as emp_name,")
                    .append(" d.factory as factory, d.gender as gender, c.name as ope_id,c.label as ope_name,")
                    .append(" d.bonus as bonus,d.attendance as attendance,d.specialty as specialty,")
                    .append(" d.audit as audit,d.discipline as discipline,d.compatibility as compatibility,d.score as score,d.classType as classType,d.operation as operation) from Performance d")
                    .append(" join Code c on d.ope_fk=c.id")
                    .append(" join OperationForeman r on d.factory=r.factory and c.id = r.ope_fk")
                    .append(" where d.isValid is true and d.factory='CEL' and c.isValid is true  and r.isValid is true and r.foreman_id ='"+user_id+"'");
        }
        else if(role.getName().equals("CMF1")||role.getName().equals("CMF2")||role.getName().equals("CMFT")||role.getName().equals("CMFS")) {
            hql =hql.append("select new map(d.id as id, d.emp_id as emp_id, d.emp_name as emp_name,")
                    .append(" d.factory as factory, d.gender as gender, c.name as ope_id,c.label as ope_name,")
                    .append(" d.bonus as bonus,d.attendance as attendance,d.specialty as specialty,")
                    .append(" d.audit as audit,d.discipline as discipline,d.compatibility as compatibility,d.score as score,d.classType as classType,d.operation as operation) from Performance d")
                    .append(" join Code c on d.ope_fk=c.id")
                    .append(" join OperationRole r on r.role_fk= "+role.getId())
                    .append(" where d.isValid is true and d.factory='CEL' and c.isValid is true and d.ope_fk = r.ope_fk");
        }
        else if(role.getName().equals("CMFG")||role.getName().equals("admin")){
            hql =hql.append("select new map(d.id as id, d.emp_id as emp_id, d.emp_name as emp_name,")
                    .append(" d.factory as factory, d.gender as gender, c.name as ope_id,c.label as ope_name,")
                    .append(" d.bonus as bonus,d.attendance as attendance,d.specialty as specialty,")
                    .append(" d.audit as audit,d.discipline as discipline,d.compatibility as compatibility,d.score as score,d.classType as classType,d.operation as operation) from Performance d")
                    .append(" join Code c on d.ope_fk=c.id")
                    .append(" where d.isValid is true and d.factory='CEL' and c.isValid is true");
        }
    	

        int paramPosition = 0;
        List<Object> params = new ArrayList<>();
        if (StringUtil.isNotEmpty(emp_id)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.emp_id like ").append(placeholder);
            params.add(HqlHelper.like(emp_id));
        }
        
        if (StringUtil.isNotEmpty(month)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.month = ").append(placeholder);
            params.add(month);
        }
        
        if (bonus!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.bonus = ").append(placeholder);
            params.add(bonus);
        }
        
        if (score!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.score = ").append(placeholder);
            params.add(score);
        }
        
        if (ope_fk!=null && ope_fk.length>0) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.ope_fk in ( ").append(placeholder).append(")");
            params.add(ope_fk);
        }
        if (StringUtil.isNotEmpty(classType)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.classType = ").append(placeholder);
            params.add(classType);
        }
        if (StringUtil.isNotEmpty(condition)) {
            hql.append(" and (").append(condition).append(")");
        }
        String queryString = hql.toString();
        List list = hibernateInvoker.find(Map.class, pno,10,queryString, params.toArray());
        List alllist = hibernateInvoker.find(Map.class,queryString, params.toArray());
    	List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
    	Map<String, Object> map = new HashMap<String, Object>();
    	map.put("allTotal", alllist.size());
    	if(allFlag==1)map.put("performance", alllist);
    	else map.put("performance", list);
    	listTemp.add(map);
		return listTemp;
    } 
    
    public List listByMon(String month){
    	StringBuilder hql = new StringBuilder();
    	hql = hql.append("select d from PerformanceMon d")
                 .append(" where d.isValid is true and d.ope_fk!=null and d.ope_fk<>'' and d.factory='CEL' ");
    	List<Object> params = new ArrayList<>();
    	int paramPosition = 0;
        if (StringUtil.isNotEmpty(month)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.month = ").append(placeholder);
            params.add(month);
        }
        String queryString = hql.toString();
    	List list = hibernateInvoker.find(PerformanceMon.class, queryString, params.toArray());
		return list;
    }
    public List listByMonForLcm(String month){
    	StringBuilder hql = new StringBuilder();
    	hql = hql.append("select d from PerformanceMon d")
                 .append(" where d.isValid is true and d.ope_fk!=null and d.ope_fk<>'' and d.factory like '%M%' ");
    	List<Object> params = new ArrayList<>();
    	int paramPosition = 0;
        if (StringUtil.isNotEmpty(month)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.month = ").append(placeholder);
            params.add(month);
        }
        String queryString = hql.toString();
    	List list = hibernateInvoker.find(PerformanceMon.class, queryString, params.toArray());
		return list;
    }
    
    public List listmaintainByMon(int allFlag,int pno,String factory,String emp_id,String emp_name,String condition,String month,Long ope_fk,String classType){
        String user_id="sys";
        if(SessionLocal.getUser()!=null)
        user_id = SessionLocal.getUser().getUser_id();
        Role role = userService.getRoleByUser(user_id);
        StringBuilder hql = new StringBuilder();
        if(role.getName().equals("FORE")){//领班
        	    hql = hql.append("select new map(d.id as id, d.factory as factory,d.emp_id as emp_id, d.emp_name as emp_name,")
                       .append(" d.gender as gender, c.name as ope_id,c.label as ope_name,")
                       .append(" d.shftHour as shftHour,d.attdHour as attdHour,d.level as level,d.count as count,")
                       .append(" d.audit as audit,d.discipline as discipline,d.compatibility as compatibility,d.classType as classType) from PerformanceMon d")
                       .append(" join Code c on d.ope_fk=c.id")
                       .append(" join OperationForeman r on d.factory=r.factory and c.id = r.ope_fk")
                       .append(" where d.isValid is true and d.ope_fk!=null and d.ope_fk<>'' and d.factory='CEL' and c.isValid is true  and r.isValid is true and r.foreman_id ='"+user_id+"'");
              
        }else if(role.getName().equals("CMF1")||role.getName().equals("CMF2")||role.getName().equals("CMFT")||role.getName().equals("CMFS")) {
        	hql = hql.append("select new map(d.id as id, d.factory as factory,d.emp_id as emp_id, d.emp_name as emp_name,")
                    .append(" d.gender as gender, c.name as ope_id,c.label as ope_name,")
                    .append(" d.shftHour as shftHour,d.attdHour as attdHour,d.level as level,d.count as count,")
                    .append(" d.audit as audit,d.discipline as discipline,d.compatibility as compatibility,d.classType as classType) from PerformanceMon d")
                    .append(" join Code c on d.ope_fk=c.id")
                    .append(" join OperationRole r on r.role_fk= "+role.getId())
                    .append(" where d.isValid is true and d.ope_fk!=null and d.ope_fk<>'' and d.factory='CEL' and c.isValid is true and d.ope_fk = r.ope_fk");
           
        }else if(role.getName().equals("CMFG")||role.getName().equals("admin")){
        	hql = hql.append("select new map(d.id as id, d.factory as factory,d.emp_id as emp_id, d.emp_name as emp_name,")
                    .append(" d.gender as gender, c.name as ope_id,c.label as ope_name,")
                    .append(" d.shftHour as shftHour,d.attdHour as attdHour,d.level as level,d.count as count,")
                    .append(" d.audit as audit,d.discipline as discipline,d.compatibility as compatibility,d.classType as classType) from PerformanceMon d")
                    .append(" join Code c on d.ope_fk=c.id")
                    .append(" where d.isValid is true and d.ope_fk!=null and d.ope_fk<>'' and d.factory='CEL' and c.isValid is true");
        }
        
    	List<Object> params = new ArrayList<>();
    	int paramPosition = 0;
        if (StringUtil.isNotEmpty(factory)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.factory = ").append(placeholder);
            params.add(factory);
        }
        if (StringUtil.isNotEmpty(emp_id)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.emp_id like ").append(placeholder);
            params.add(HqlHelper.like(emp_id));
        }
        if (StringUtil.isNotEmpty(emp_name)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.emp_name = ").append(placeholder);
            params.add(emp_name);
        }
        if (StringUtil.isNotEmpty(month)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.month = ").append(placeholder);
            params.add(month);
        }
        if (ope_fk!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.ope_fk = ").append(placeholder);
            params.add(ope_fk);
        }
        if (StringUtil.isNotEmpty(classType)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.classType = ").append(placeholder);
            params.add(classType);
        }
        if (StringUtil.isNotEmpty(condition)) {
            hql.append(" and (").append(condition).append(")");
        }
        String queryString = hql.toString();
        List list = hibernateInvoker.find(Map.class,pno,10, queryString, params.toArray());
        List alllist = hibernateInvoker.find(Map.class, queryString, params.toArray());
    	List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
    	Map<String, Object> map = new HashMap<String, Object>();
    	map.put("allTotal", alllist.size());
    	if(allFlag==1)map.put("performanceMon", alllist);
    	else map.put("performanceMon", list);
    	listTemp.add(map);
		return listTemp;
    	
    }

    @Transactional
	public String updateByMon(String month,String emp_id, String discipline, String compatibility,String user_id) {
    	String result="";
    	PerformanceMon objPerformanceMon = performanceMonManager.findUnique("from PerformanceMon p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
    	if(objPerformanceMon==null){
    		result="1";
    	}
    	else{
    		objPerformanceMon.setDiscipline(Double.parseDouble(discipline));
    		objPerformanceMon.setCompatibility(Double.parseDouble(compatibility));
    		performanceMonManager.save(objPerformanceMon);
    		result="0";
    	}
		return result;
    	
	}
	
	
	
	@Transactional
 	public String updateBonus(List<Map<String, String>> list) {
		String result = "0";
		 
	 for(int i = 0; i < list.size(); i++) {
		Map<String, String> jsonObject = list.get(i);
    	    String month = jsonObject.get("month");
    	    String emp_id = jsonObject.get("emp_id");
    	    String bonus = jsonObject.get("bonus");
    	    Performance objPerformanceMon = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
    	    if(objPerformanceMon==null){
         		result="1";
         		return result;
         	}
         	else{
         		objPerformanceMon.setBonus(Double.parseDouble(bonus));
         		performanceManager.save(objPerformanceMon);
         		result="0";
         	}
     	}
     	
 		return result;
     	
 	}
	
	
    
    @Transactional
 	public String updateCapacityByMon(List<Map<String, String>> list) {
		String result = "0";
		 
	 for(int i = 0; i < list.size(); i++) {
		Map<String, String> jsonObject = list.get(i);
    	    String month = jsonObject.get("month");
    	    String emp_id = jsonObject.get("emp_id");
    	    String capacity = jsonObject.get("capacity");
    	    Performance objPerformanceMon = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
    	    if(objPerformanceMon==null){
         		result="1";
         		return result;
         	}
         	else{
         		objPerformanceMon.setCapacity(Double.parseDouble(capacity));
         		performanceManager.save(objPerformanceMon);
         		result="0";
         	}
     	}
     	
 		return result;
     	
 	}
    
    @Transactional
 	public String updateCompatibilityByMon(List<Map<String, String>> list) {
		String result = "0";
		 
	 for(int i = 0; i < list.size(); i++) {
		Map<String, String> jsonObject = list.get(i);
    	    String month = jsonObject.get("month");
    	    String emp_id = jsonObject.get("emp_id");
    	    String compatibility = jsonObject.get("compatibility");
    	    Performance objPerformanceMon = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
    	    if(objPerformanceMon==null){
         		result="1";
         		return result;
         	}
         	else{
         		objPerformanceMon.setCompatibility(Double.parseDouble(compatibility));
         		performanceManager.save(objPerformanceMon);
         		result="0";
         	}
     	}
     	
     	
 		return result;
     	
 	}
    
    
    
    
    @Transactional
 	public String updateQuality(List<Map<String, String>> list) {
		String result = "0";
		 
	 for(int i = 0; i < list.size(); i++) {
		Map<String, String> jsonObject = list.get(i);
    	    String month = jsonObject.get("month");
    	    String emp_id = jsonObject.get("emp_id");
    	    String quality = jsonObject.get("quality");
    	    Performance objPerformanceMon = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
    	    if(objPerformanceMon==null){
         		result="1";
         		return result;
         	}
         	else{
         		objPerformanceMon.setSpecialty(Double.parseDouble(quality));
         		performanceManager.save(objPerformanceMon);
         		result="0";
         	}
     	}
     	
     	
 		return result;
     	
 	}
    
    
    
    @Transactional
 	public String updateDiscipline(List<Map<String, String>> list) {
		String result = "0";
		 
	 for(int i = 0; i < list.size(); i++) {
		Map<String, String> jsonObject = list.get(i);
    	    String month = jsonObject.get("month");
    	    String emp_id = jsonObject.get("emp_id");
    	    String discipline = jsonObject.get("discipline");
    	    Performance objPerformanceMon = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
    	    if(objPerformanceMon==null){
         		result="1";
         		return result;
         	}
         	else{
         		objPerformanceMon.setDiscipline(Double.parseDouble(discipline));
         		performanceManager.save(objPerformanceMon);
         		result="0";
         	}
     	}
     	
     	
 		return result;
     	
 	}
    
    
    
    
    @Transactional
 	public String updateLearns(List<Map<String, String>> list) {
		String result = "0";
		 
	 for(int i = 0; i < list.size(); i++) {
		Map<String, String> jsonObject = list.get(i);
    	    String month = jsonObject.get("month");
    	    String emp_id = jsonObject.get("emp_id");
    	    String learns = jsonObject.get("learns");
    	    Performance objPerformanceMon = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
    	    if(objPerformanceMon==null){
         		result="1";
         		return result;
         	}
         	else{
         		objPerformanceMon.setLearns(Double.parseDouble(learns));
         		performanceManager.save(objPerformanceMon);
         		result="0";
         	}
     	}
     	
	 
 		return result;
     	
 	}
    
    
    @Transactional
 	public String updateProfessional(List<Map<String, String>> list) {
		String result = "0";
		 
	 for(int i = 0; i < list.size(); i++) {
		Map<String, String> jsonObject = list.get(i);
    	    String month = jsonObject.get("month");
    	    String emp_id = jsonObject.get("emp_id");
    	    String professional = jsonObject.get("professional");
    	    Performance objPerformanceMon = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
    	    if(objPerformanceMon==null){
         		result="1";
         		return result;
         	}
         	else{
         		objPerformanceMon.setLearns(Double.parseDouble(professional));
         		performanceManager.save(objPerformanceMon);
         		result="0";
         	}
     	}
     	
 		return result;
     	
 	}
    
    
	public List listmaintainByDay(int allFlag,int pno,String factory, String emp_id, String emp_name,String from_date,String to_date,Long ope_id,Long type,Long auditType,String condition) throws ParseException {
    	List<Object> params = new ArrayList<>();
        StringBuilder hql = new StringBuilder("select new map(d.id as id, d.factory as factory,d.category as category,d.fab_date as fab_date,c.name as type,")
                .append(" b.name as ope_id,b.label as ope_name ,d.emp_id as emp_id, d.emp_name as emp_name, d.group_leader as group_leader,d.class_type as class_type,d.reason as reason) from PerformanceDay d")
                .append(" join Code c on d.type=c.id")
                .append(" join Code b on d.ope_fk=b.id")
                .append(" where d.isValid is true and c.isValid is true and b.isValid is true AND d.factory='CEL' and d.category='操作品质' ");
        
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        Date fromdate = null;
        if (StringUtil.isNotEmpty(from_date)) fromdate = sdf.parse(from_date);
        Date todate = null;
        if (StringUtil.isNotEmpty(to_date)) todate = sdf.parse(to_date);
        
        int paramPosition = 0;
        if (StringUtil.isNotEmpty(factory)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.factory = ").append(placeholder);
            params.add(factory);
        }
        if (StringUtil.isNotEmpty(emp_id)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.emp_id = ").append(placeholder);
            params.add(emp_id);
        }
        if (StringUtil.isNotEmpty(emp_name)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.emp_name = ").append(placeholder);
            params.add(emp_name);
        }
        if (StringUtil.isNotEmpty(from_date)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.fab_date >= ").append(placeholder);
            params.add(fromdate);
        }
        if (StringUtil.isNotEmpty(to_date)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.fab_date <= ").append(placeholder);
            params.add(todate);
        }
        if (ope_id!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.ope_fk = ").append(placeholder);
            params.add(ope_id);
        }
        if (type!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.type = ").append(placeholder);
            params.add(type);
        }
        if (auditType!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.type = ").append(placeholder);
            params.add(auditType);
        }
        if (StringUtil.isNotEmpty(condition)) {
            hql.append(" and (").append(condition).append(")");
        }
//        hql.append("order by d.fab_date desc");
        String queryString = hql.toString();
        List list = hibernateInvoker.find(Map.class, pno,10,queryString, params.toArray());
        List alllist = hibernateInvoker.find(Map.class, queryString, params.toArray());
    	List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
    	Map<String, Object> map = new HashMap<String, Object>();
    	map.put("allTotal", alllist.size());
    	if(allFlag==1)map.put("performanceDay", alllist);
    	else map.put("performanceDay", list);
    	listTemp.add(map);
		return listTemp;
	}
    
	public List listAudit(int allFlag,int pno,String factory, String emp_id, String emp_name,String from_date,String to_date,Long ope_id,Long type,Long auditType,String condition) throws ParseException {
    	List<Object> params = new ArrayList<>();
        StringBuilder hql = new StringBuilder("select new map(d.id as id, d.factory as factory,d.category as category,d.fab_date as fab_date,c.name as type,")
                .append(" b.name as ope_id,b.label as ope_name ,d.emp_id as emp_id, d.emp_name as emp_name, d.group_leader as group_leader,d.class_type as class_type,d.reason as reason) from PerformanceDay d")
                .append(" join Code c on d.type=c.id")
                .append(" join Code b on d.ope_fk=b.id")
                .append(" where d.isValid is true and c.isValid is true and b.isValid is true AND d.factory='CEL' and d.category='稽核管理' ");
        
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        Date fromdate = null;
        if (StringUtil.isNotEmpty(from_date)) fromdate = sdf.parse(from_date);
        Date todate = null;
        if (StringUtil.isNotEmpty(to_date)) todate = sdf.parse(to_date);
        
        int paramPosition = 0;
        if (StringUtil.isNotEmpty(factory)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.factory = ").append(placeholder);
            params.add(factory);
        }
        if (StringUtil.isNotEmpty(emp_id)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.emp_id = ").append(placeholder);
            params.add(emp_id);
        }
        if (StringUtil.isNotEmpty(emp_name)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.emp_name = ").append(placeholder);
            params.add(emp_name);
        }
        if (StringUtil.isNotEmpty(from_date)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.fab_date >= ").append(placeholder);
            params.add(fromdate);
        }
        if (StringUtil.isNotEmpty(to_date)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.fab_date <= ").append(placeholder);
            params.add(todate);
        }
        if (ope_id!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.ope_fk = ").append(placeholder);
            params.add(ope_id);
        }
        if (type!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.type = ").append(placeholder);
            params.add(type);
        }
        if (auditType!=null) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.type = ").append(placeholder);
            params.add(auditType);
        }
        if (StringUtil.isNotEmpty(condition)) {
            hql.append(" and (").append(condition).append(")");
        }
//        hql.append("order by d.fab_date desc");
        String queryString = hql.toString();
        List list = hibernateInvoker.find(Map.class, pno,10,queryString, params.toArray());
        List alllist = hibernateInvoker.find(Map.class, queryString, params.toArray());
    	List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
    	Map<String, Object> map = new HashMap<String, Object>();
    	map.put("allTotal", alllist.size());
    	if(allFlag==1)map.put("performanceDay", alllist);
    	else map.put("performanceDay", list);
    	listTemp.add(map);
		return listTemp;
	}
	
	public PerformanceMon getPerformanceMon(String month,String emp_id){
		String hql = "select u from PerformanceMon u  where u.isValid=true and u.month=?0 and u.emp_id=?1 ";
		return performanceMonManager.findUnique(hql, month,emp_id);
	}
	public OperationForeman getOperationForeman(String fab_id,String ope_id,String user_id) {
		String hql = "select u from OperationForeman u  where u.isValid=true and u.factory=?0 and u.ope_id=?1 and u.foreman_id=?2";
		return operationForemanManager.findUnique(hql,fab_id, ope_id,user_id);
		
	}
	
	@Transactional
	public String createByDay(String factory, String category, String fabDate, Long type, Long ope_fk, String emp_id,
			String emp_name, String group_leader, String class_type,String reason) throws ParseException {
		String result="0";
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM-dd");
		Date fab_Date = null;
		if(StringUtil.isNotEmpty(fabDate)){
			if(fabDate.length()>10)
			fab_Date = sdf.parse(fabDate);
			else fab_Date = sdf1.parse(fabDate);
		}
		PerformanceDay objPerformanceDay= new PerformanceDay();
		objPerformanceDay.setFactory(factory);
		objPerformanceDay.setCategory(category);
		objPerformanceDay.setFab_date(fab_Date);
		objPerformanceDay.setType(type);
		objPerformanceDay.setOpe_fk(ope_fk);
		objPerformanceDay.setEmp_id(emp_id);
		objPerformanceDay.setEmp_name(emp_name);
		objPerformanceDay.setGroup_leader(group_leader);
		objPerformanceDay.setClass_type(class_type);
		objPerformanceDay.setReason(reason);
		performanceDayManager.save(objPerformanceDay);
		result = objPerformanceDay.getId().toString();
		return result;
	}
	
	@Transactional
	public String createMajorLoss(String fabDate, Long type, Long ope_fk, String emp_id,
			String emp_name,String reason) throws ParseException {
		String result="0";
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM-dd");
		Date fab_Date = null;
		if(StringUtil.isNotEmpty(fabDate)){
			if(fabDate.length()>10)
			fab_Date = sdf.parse(fabDate);
			else fab_Date = sdf1.parse(fabDate);
		}
		PerformanceDay objPerformanceDay= new PerformanceDay();
		objPerformanceDay.setFactory("ARY");
		Code C = hibernateInvoker.findUnique(Code.class, "from Code c where c.id = '" + type + "'");
		objPerformanceDay.setCategory(C.getName());
		objPerformanceDay.setFab_date(fab_Date);
		objPerformanceDay.setType(type);
		objPerformanceDay.setOpe_fk(ope_fk);
		objPerformanceDay.setEmp_id(emp_id);
		objPerformanceDay.setEmp_name(emp_name);
		objPerformanceDay.setReason(reason);
		performanceDayManager.save(objPerformanceDay);
		result = objPerformanceDay.getId().toString();
		return result;
	}
	
	@Transactional
	public int deleteByDay(Long id) {
		int i=0;
		i = performanceDayManager.delete(id);
		return i;
	}
	@Transactional
	public void deleteByDayByFabdate(String date) {
	    String hql ="select u.id from PerformanceDay u where u.isValid=true and u.category='稽核管理'"
	    		+ " and u.factory='CEL' and date_format(u.fab_date,'%Y-%m-%d')>=?0 ";
	    List<Long> list = hibernateInvoker.find(Long.class, hql, date);
	    Long[] ids = (Long[])list.toArray(new Long[list.size()]);
	    performanceDayManager.delete(ids);
	    
	}

	public Performance getPerformance(String month, String emp_id,Long ope_fk) {
		String hql="select u from Performance u where u.isValid=true and u.month=?0 and u.emp_id=?1 and u.ope_fk=?2";
		return (Performance) hibernateInvoker.findUnique(hql, month,emp_id,ope_fk);
	}

	public Performance getPerformance(String month, String emp_id) {
		String hql="select u from Performance u where u.isValid=true and u.month=?0 and u.emp_id=?1";
		return (Performance) hibernateInvoker.findUnique(hql, month,emp_id);
	}
	
	public List<Performance> list(String month) {
    	StringBuilder hql = new StringBuilder();
    	hql = hql.append("select d from Performance d")
                 .append(" where d.isValid is true and d.factory='CEL' ");
    	List<Object> params = new ArrayList<>();
    	int paramPosition = 0;
        if (StringUtil.isNotEmpty(month)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.month = ").append(placeholder);
            params.add(month);
        }
        String queryString = hql.toString();
    	List list = hibernateInvoker.find(Performance.class, queryString, params.toArray());
		return list;
	}

	public List getOperation(String fromDate, String toDate) throws ParseException {
	   	List<Object> params = new ArrayList<>();
        StringBuilder hql = new StringBuilder("select new map(d.category as category,d.emp_id as emp_id,d.ope_fk as ope_fk,d.type as type,count(*) as count) from PerformanceDay d")
                .append(" join Code c on d.type=c.id")
                .append(" where d.isValid is true and c.isValid is true and d.factory='CEL' and d.category='操作品质' ");
        
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        Date fromdate = null;
        if (StringUtil.isNotEmpty(fromDate)) fromdate = sdf.parse(fromDate);
        Date todate = null;
        if (StringUtil.isNotEmpty(toDate)) todate = sdf.parse(toDate);
        
        int paramPosition = 0;

        if (StringUtil.isNotEmpty(fromDate)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.fab_date >= ").append(placeholder);
            params.add(fromdate);
        }
        if (StringUtil.isNotEmpty(toDate)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.fab_date < ").append(placeholder);
            params.add(todate);
        }
        hql.append(" group by d.category,d.emp_id,d.ope_fk,d.type");
        String queryString = hql.toString();
        List list = hibernateInvoker.find(Map.class, queryString, params.toArray());
		return list;
	}
	public List getOperation1(String fromDate, String toDate) throws ParseException {
	   	List<Object> params = new ArrayList<>();
        StringBuilder hql = new StringBuilder("select new map(d.category as category,d.emp_id as emp_id,d.ope_fk as ope_fk,d.type as type,count(*) as count) ")
                .append(" from PerformanceDay d join Code c on d.type=c.id")
                .append(" where d.isValid is true and c.isValid is true and d.factory='CEL' and d.category='稽核管理' ");
        
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        Date fromdate = null;
        if (StringUtil.isNotEmpty(fromDate)) fromdate = sdf.parse(fromDate);
        Date todate = null;
        if (StringUtil.isNotEmpty(toDate)) todate = sdf.parse(toDate);
        
        int paramPosition = 0;

        if (StringUtil.isNotEmpty(fromDate)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.fab_date >= ").append(placeholder);
            params.add(fromdate);
        }
        if (StringUtil.isNotEmpty(toDate)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and d.fab_date < ").append(placeholder);
            params.add(todate);
        }
        hql.append(" group by d.category,d.emp_id,d.ope_fk,d.type");
        String queryString = hql.toString();
        List list = hibernateInvoker.find(Map.class, queryString, params.toArray());
		return list;
	}
	public Object getAverageBouns(String month, Long[] ope_fk,String classType) {
		
//		if(ope_fk==null && month==null) {
//	        hql = hql.append("SELECT sum(p.bonus)/count(*) as average FROM Performance p");
//	 		return hibernateInvoker.findUnique(hql.toString());
//		}
//		else if(ope_fk==null){
//	        hql = hql.append("SELECT sum(p.bonus)/count(*) as average FROM Performance p where p.month =?0 ");
//	 		return hibernateInvoker.findUnique(hql.toString(), month);
//		}
//		else if(month==null){
//	        hql = hql.append("SELECT sum(p.bonus)/count(*) as average FROM Performance p where p.ope_fk =?0 ");
//	 		return hibernateInvoker.findUnique(hql.toString(), ope_fk);
//		}else {
//	        hql = hql.append("SELECT sum(p.bonus)/count(*) as average FROM Performance p where p.month=?0 and p.ope_fk =?1 ");
//	 		return hibernateInvoker.findUnique(hql.toString(), month,ope_fk);
//		}
        StringBuilder hql = new StringBuilder("SELECT sum(p.bonus)/count(*) as average FROM Performance p where p.isValid is true and p.factory='CEL' ");
		
        int paramPosition = 0;
        List<Object> params = new ArrayList<>();
        if (StringUtil.isNotEmpty(month)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and p.month = ").append(placeholder);
            params.add(month);
        }
        if (ope_fk!=null && ope_fk.length>0) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and p.ope_fk in ( ").append(placeholder).append(")");
            params.add(ope_fk);
        }
        if (StringUtil.isNotEmpty(classType)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and p.classType = ").append(placeholder);
            params.add(classType);
        }
        
        return hibernateInvoker.findUnique(hql.toString(),params.toArray());
	}
	
/*	public double getBonusGap(String month, Long ope_fk,String scope) {
		String user_id="sys";
		if(SessionLocal.getUser()!=null)
		    user_id = SessionLocal.getUser().getUser_id();
		String operations = userService.getPermissionsByUser(user_id) == null ? null:userService.getPermissionsByUser(user_id).getOpe_fk();
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null) return 0;
		StringBuilder hql = new StringBuilder();
		hql.append(" select sum(p.bonus)/count(*) as average from Performance p where p.isValid is true "
				+ "and p.factory in ('M1','M2')");
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		if(StringUtil.isNotEmpty(scope)){
			if(scope.equals("24")){
				hql.append(" and p.factory = 'M1' ");
			}else if(scope.equals("25")){
				hql.append(" and p.factory = 'M2' ");
			}
			
		}
		
		if(!role.getName().equals("admin")){
			if(!operations.contains(ope_fk + "")){
				return 0.0;
			}
			if((ope_fk != 0 && operations.contains(ope_fk + ""))){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			if(ope_fk == 0){
				String [] opeArray = operations.trim().split("\\s+");
				String ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in "  + ope_id);
			}
		}else if(role.getName().equals("admin")){
			if((ope_fk != 0)){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			//hql.append(" and p.ope_fk = '" + ope_fk + "' ");
		}
		
		
		return hibernateInvoker.findUnique(Double.class, hql.toString())==null?0:hibernateInvoker.findUnique(Double.class, hql.toString());
	}*/
	
	
	public long getCategory(long ope_fk){
		Code co = hibernateInvoker.findUnique(Code.class, "from Code where id = " + ope_fk + " and isValid = true");
		return co.getCategory();
	}
	
	
	public long getOpe_fk(String user_id){
		PerformanceMon pm = hibernateInvoker.findUnique(PerformanceMon.class, "from PerformanceMon where emp_id = " + user_id + " and month = " + DateUtil.formatDate(new Date()) + " and isValid = true");
		return pm.getOpe_fk();
	}
	
	
	public double getLcmAllBonuses(String month, Long ope_fk,String scope,String jsp) {
		String user_id="sys";
		String userFactory = "";
	    user_id = SessionLocal.getUser().getUser_id();
	    userFactory = SessionLocal.getUser().getFactory();
	    String operations = userService.getPermissionsByUser(user_id) == null ? null:userService.getPermissionsByUser(user_id).getOpe_fk();
	    Role role = userService.getRoleByUser(user_id);
		StringBuilder hql = new StringBuilder();
		hql.append(" from Performance p join Code c on c.id = p.ope_fk where p.isValid is true and p.factory like '%M%'");
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		if(role.getName().equals("admin")){
			if(jsp.equals("EngineeringJSP")){
				if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
					if(scope.equals("38")){
						hql.append(" and c.category = '38' ");
					}else if(scope.equals("39")){
						hql.append(" and c.category = '39' ");
					}
				}else{
					hql.append(" and c.category in ('38','39') ");
				}
			}else{
				if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
					if(scope.equals("24")){
						hql.append(" and c.category = '24' ");
					}else if(scope.equals("25")){
						hql.append(" and c.category = '25' ");
					}
				}else{
					hql.append(" and c.category in ('24','25') ");
				}
				
			}
		}else{
			if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
				
				if(scope.equals("24")){
					hql.append(" and c.category = '24' ");
				}else if(scope.equals("25")){
					hql.append(" and c.category = '25' ");
				}else if(scope.equals("38")){
					hql.append(" and c.category = '38' ");
				}else if(scope.equals("39")){
					hql.append(" and c.category = '39' ");
				}
			}else{
				if(userFactory.contains("制造")){
					hql.append(" and c.category in ('24','25') ");
				}else{
					hql.append(" and c.category in ('38','39') ");
				}
			}
		}
		
		if(!role.getName().equals("admin")){
			if(!operations.contains(ope_fk + "") && ope_fk != 0){
				return 0.0;
			}
			if((ope_fk != 0 && operations.contains(ope_fk + ""))){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			if(ope_fk == 0){
				String [] opeArray = operations.trim().split("\\s+");
				String ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in "  + ope_id);
			}
		}else if(role.getName().equals("admin")){
			if(ope_fk != 0){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			
		}
		List<Performance> list = hibernateInvoker.find(Performance.class, hql.toString());
		return list.size() * 100.0;
	}
	
	public double getLcmActualBonus(String month, Long ope_fk,String scope,String jsp) {
		String user_id="sys";
		String userFactory = "";
		userFactory = SessionLocal.getUser().getFactory();
	    user_id = SessionLocal.getUser().getUser_id();
	    String operations = userService.getPermissionsByUser(user_id) == null ? null:userService.getPermissionsByUser(user_id).getOpe_fk();
	    Role role = userService.getRoleByUser(user_id);
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(p.bonus) as allbonus from Performance p join Code c on c.id = p.ope_fk where p.isValid is true and p.factory like '%M%'");
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
			
			if(scope.equals("24")){
				hql.append(" and c.category = '24' ");
			}else if(scope.equals("25")){
				hql.append(" and c.category = '25' ");
			}else if(scope.equals("38")){
				hql.append(" and c.category = '38' ");
			}else if(scope.equals("39")){
				hql.append(" and c.category = '39' ");
			}
				}else{
					if(userFactory.contains("制造")){
						hql.append(" and c.category in ('24','25') ");
					}else{
						hql.append(" and c.category in ('38','39') ");
					}
				}
		if(!role.getName().equals("admin")){
			if(!operations.contains(ope_fk + "") && ope_fk != 0){
				return 0.0;
			}
			if((ope_fk != 0 && operations.contains(ope_fk + ""))){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			if(ope_fk == 0){
				String [] opeArray = operations.trim().split("\\s+");
				String ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in "  + ope_id);
			}
		}else if(role.getName().equals("admin")){
			if(ope_fk != 0){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			
		}
		return hibernateInvoker.findUnique(Double.class, hql.toString())==null?0:hibernateInvoker.findUnique(Double.class, hql.toString());
	}
	
	@Transactional
	public void deleteByDayByFabdate(String strfromdate, String strtodate) throws ParseException {
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        Date fromdate = null;
        if (StringUtil.isNotEmpty(strfromdate)) fromdate = sdf.parse(strfromdate);
        Date todate = null;
        if (StringUtil.isNotEmpty(strtodate)) todate = sdf.parse(strtodate);
		
	    String hql ="select u.id from PerformanceDay u where u.isValid=true and u.category='稽核管理' and u.factory='CEL' and u.fab_date between ?0 and ?1 ";
	    List<Long> list = hibernateInvoker.find(Long.class, hql,fromdate,todate);
	    Long[] ids = (Long[])list.toArray(new Long[list.size()]);
	    performanceDayManager.delete(ids);
		
	}
	
	
	
	public List getUser_ID (String emp_id) throws ParseException {
		
		String factory = SessionLocal.getUser().getFactory();
		String hql = " select new map(u.user_id as user_id,u.user_name as user_name) from User u  where u.isValid = true";
		if(factory.contains("M")){
			hql = hql + " and u.factory like '%M%' ";
		}else{
			hql = hql + " and u.factory = '" + factory + "' ";
		}
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql += " and u.user_id like '" + emp_id + "%' ";
		}
		List list = hibernateInvoker.find(Map.class, hql);
		List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("User_ID", list);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListLcmAttendenceThisMon (String emp_id,String month,String ope_id,String scope,String classtype) throws ParseException {
		String userFactory="";
		String user_id="sys";
	    if(SessionLocal.getUser()!=null)
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null) return null;
		if(SessionLocal.getUser()!=null)
			userFactory = SessionLocal.getUser().getFactory();
		String hql = " select new map(p.factory as factory,c.name as name,c.label as label,p.emp_id as emp_id,p.emp_name as emp_name,"
				+ " p.gender as gender,p.shftHour as shftHour,p.attdHour as attdHour, pe.attendance as attendance) "
				+ " from PerformanceMon p join Code c on c.id = p.ope_fk join Performance pe on p.emp_id = pe.emp_id where p.isValid = true ";
		
		
		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
				
				if(scope.equals("24")){
					hql += " and c.category = '24' ";
				}else if(scope.equals("25")){
					hql += " and c.category = '25' ";
				}else if(scope.equals("38")){
					hql += " and c.category = '38' ";
				}else if(scope.equals("39")){
					hql += " and c.category = '39' ";
				}
			}else{
				if(userFactory.contains("制造")){
					hql += " and c.category in ('24','25') ";
				}else{
					hql += " and c.category in ('38','39') ";
				}
			}
		}else{
				if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
				
				if(scope.equals("24")){
					hql += " and c.category = '24' ";
				}else if(scope.equals("25")){
					hql += " and c.category = '25' ";
				}else if(scope.equals("38")){
					hql += " and c.category = '38' ";
				}else if(scope.equals("39")){
					hql += " and c.category = '39' ";
				}
			}
		}
		
		
		
		if(StringUtil.isNotEmpty(classtype)){
			if("null".equals(classtype)){
				hql += " and (pe.classType is null or pe.classType = '')";
			}else{
				hql += " and pe.classType ='" + classtype + "'";
			}
			
		}
		
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql += " and p.emp_id ='" + emp_id + "'";
		}
		if(StringUtil.isNotEmpty(month)){
			hql += " and p.month ='" + month + "'";
			hql += " and pe.month ='" + month + "'";
				}
		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				if(userService.getPermissionsByUser(user_id).getOpe_fk().contains(ope_id)){
					hql += " and p.ope_fk = '" + ope_id + "' ";
				}else{
					return null;
				}
			}else{
				String [] opeArray = userService.getPermissionsByUser(user_id).getOpe_fk().trim().split("\\s+");
				ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql += " and p.ope_fk in " + ope_id;
			}
		}else if(role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				hql += " and p.ope_fk = '" + ope_id + "' ";
			}
		}
		
		hql += " order by p.emp_id";
		
		List alllist = hibernateInvoker.find(Map.class, hql);
		List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    map.put("attendence", alllist);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	@SuppressWarnings({ "rawtypes", "unused" })
	public List getListCapacityThisMon (String emp_id,String month,String scope,String[] ope_id,String classtype) throws ParseException {
		String userFactory="";
		String user_id="sys";
	    if(SessionLocal.getUser()!=null)
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null) return null;
	    OperationForeman opForman = userService.getOpeIDByUser(user_id);
		
		if(SessionLocal.getUser()!=null)
			userFactory = SessionLocal.getUser().getFactory();
		String hql = "select new map(pm.factory as factory, c.label as label, pm.emp_id as emp_id, pm.output/pm.cnlow as capacityRatio,"
				+ "pm.emp_name as emp_name, pm.gender as gender, pm.output as output, pm.cnlow as cnlow,p.capacity as capacity) "
				+ "from PerformanceMon pm join Performance p on pm.emp_id = p.emp_id "
				+ "join Code c on c.id = pm.ope_fk where pm.isValid = true and pm.ope_fk is NOT NULL ";
		String opeid = "";

		
		if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
			if(scope.equals("24")){
				hql += " and pm.factory ='M1'";
			}else if(scope.equals("25")){
				hql += " and pm.factory ='M2'";
			}
				}else{
					hql += " and pm.factory in ('M1','M2') ";
				}
		if(StringUtil.isNotEmpty(month)){
			hql += " and pm.month ='" + month + "'";
			hql += " and p.month ='" + month + "'";
		}
		
		if(!role.getName().equals("admin")){
			String operations = userService.getPermissionsByUser(user_id).getOpe_fk();
			if(ope_id  != null && ope_id.length > 0){
				int count = 1;
				opeid += " (";
				for(String s : ope_id){
					if(!operations.contains(s))return null;
					if(count == ope_id.length){
						opeid += "'" + s + "')";
					}else{
						opeid += "'" + s + "',";
						count ++;
					}
				}
				hql += " and pm.ope_fk in " + opeid;
			}else{
				String [] opeArray = operations.trim().split("\\s+");
				opeid += " (";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						opeid = opeid + "'" + opeArray[j] + "')";
					}else{
						opeid = opeid + "'" + opeArray[j] + "',";
					}
				}
				hql += " and pm.ope_fk in " + opeid;
			}
		}else if(role.getName().equals("admin")){
			if(ope_id  != null && ope_id.length > 0){
				int count = 1;
				opeid += " (";
				for(String s : ope_id){
					if(count == ope_id.length){
						opeid += "'" + s + "')";
					}else{
						opeid += "'" + s + "',";
						count ++;
					}
				}
				hql += " and pm.ope_fk in " + opeid;
			}
		}

		
		if(StringUtil.isNotEmpty(classtype)){
			if("null".equals(classtype)){
				hql += " and (p.classType is null or p.classType = '')";
			}else{
				hql += " and p.classType ='" + classtype + "'";
			}
			
		}
		
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql += " and pm.emp_id ='" + emp_id + "'";
		}
		
		
		hql += " order by p.emp_id ";
		List alllist = hibernateInvoker.find(Map.class, hql);
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    map.put("actualCapacity", alllist);
	    listTemp.add(map);
		return listTemp;
	}	
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List getListQualityThisMon (String emp_id,String month,String ope_id,String scope,String category,String classtype)  {
		String user_id="sys";
	    if(SessionLocal.getUser()!=null)
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null) return null;
		StringBuffer hql = new StringBuffer();
		if(StringUtil.isNotEmpty(category)){
			if(category.equals("0")){
				hql.append(" select new map(p.factory as factory,c.label as label,p.emp_id as emp_id,p.emp_name as emp_name,"
						+ "p.month as date,pe.specialty as specialty) "
						+ "from PerformanceMon p join Code c on c.id = p.ope_fk join Performance pe on p.emp_id = pe.emp_id "
						+ "where p.isValid = true  and p.factory in ('M1','M2')");
			}else{
				if(category.equals("34")){
					hql.append(" select new map(p.category as category,p.fab_date as fab_date,p.factory as factory,c.label as label,p.emp_id as emp_id,");
					hql.append(" p.emp_name as emp_name,p.output as output,p.ng as ng,p.dppm as dppm) from PerformanceDay p join Code c on c.id = p.ope_fk ");
					hql.append(" where p.isValid = true and c.isValid = true and p.type = '34' ");
				}else{
					hql.append(" select new map(p.category as category,p.fab_date as fab_date,p.factory as factory,c.label as label,p.emp_id as emp_id,");
					hql.append(" p.emp_name as emp_name,p.reason as reason) from PerformanceDay p join Code c on c.id = p.ope_fk ");
					hql.append(" where p.isValid = true and c.isValid = true and p.type = '" + category + "' ");
				}
				
			}
		}
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}
		if(StringUtil.isNotEmpty(month)){
			if(category.equals("0")){
				hql.append(" and p.month = '" + month + "' ");
				hql.append(" and pe.month = '" + month + "' ");
			}else{
				Calendar calendar = Calendar.getInstance();
				Date fab_date = DateUtil.parseDate(month + "-15");
				calendar.setTime(fab_date);
				String dateForm = (calendar.get(Calendar.YEAR) + "") + "-" + (calendar.get(Calendar.MONTH) + "") + "-16";
				String dateTo = DateUtil.formatDate(fab_date);
				hql.append(" and p.fab_date >= '"+ dateForm + "' and p.fab_date <= '"+ dateTo + "'");
			}
		}

		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				if(userService.getPermissionsByUser(user_id).getOpe_fk().contains(ope_id)){
					hql.append(" and p.ope_fk = '" + ope_id + "' ");
				}else{
					return null;
				}
			}else{
				String [] opeArray = userService.getPermissionsByUser(user_id).getOpe_fk().trim().split("\\s+");
				ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in " + ope_id);
			}
		}else if(role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				hql.append(" and p.ope_fk = '" + ope_id + "' ");
			}
		}

		if(StringUtil.isNotEmpty(classtype) && category.equals("0")){
			if("null".equals(classtype)){
				hql.append(" and (p.classType is null or p.classType = '')");
			}else{
				hql.append(" and pe.classType ='" + classtype + "'");
			}
			
		}
		
		
		if(StringUtil.isNotEmpty(scope)){
			if(scope.equals("24")){
				hql.append(" and p.factory = 'M1' ");
			}else if(scope.equals("25")){
				hql.append(" and p.factory = 'M2' ");
			}
		}
		
		hql.append(" order by p.emp_id ");
		
		List alllist = hibernateInvoker.find(Map.class, hql.toString());
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    map.put("quality", alllist);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	public List getLCMPerformance(String emp_id,String month,String ope_id,String score,String bonus,String scope,String classtype,String jsp){
		String user_id="sys";
		String userFactory="";
			
	    if(SessionLocal.getUser()!=null){
	    	userFactory = SessionLocal.getUser().getFactory();
	    	user_id = SessionLocal.getUser().getUser_id();
	    }
	    
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null) return null;
		StringBuffer hql = new StringBuffer();
		hql.append(" select new map(c.label as label,p.emp_id as emp_id,p.emp_name as emp_name,"
				+ "p.gender as gender,p.bonus as bonus,p.attendance as attendance,"
				+ "p.capacity as capacity,p.compatibility as compatibility,p.specialty as specialty,"
				+ "p.discipline as discipline,p.professional as professional,p.learns as learns,"
				+ "p.score as score) from Performance p join Code c on c.id = p.ope_fk "
				+ " where p.factory like '%M%' and p.isValid = true");
		
		
		
		
		if(role.getName().equals("admin")){
			
			if(jsp.equals("EngineeringJSP")){
				if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
					if(scope.equals("38")){
						hql.append(" and c.category = '38' ");
					}else if(scope.equals("39")){
						hql.append(" and c.category = '39' ");
					}
				}else{
					hql.append(" and c.category in ('38','39') ");
				}
			}else{
				if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
					if(scope.equals("24")){
						hql.append(" and c.category = '24' ");
					}else if(scope.equals("25")){
						hql.append(" and c.category = '25' ");
					}
				}else{
					hql.append(" and c.category in ('24','25') ");
				}
				
			}
		}else{
			if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
				
				if(scope.equals("24")){
					hql.append(" and c.category = '24' ");
				}else if(scope.equals("25")){
					hql.append(" and c.category = '25' ");
				}else if(scope.equals("38")){
					hql.append(" and c.category = '38' ");
				}else if(scope.equals("39")){
					hql.append(" and c.category = '39' ");
				}
			}else{
				if(userFactory.contains("制造")){
					hql.append(" and c.category in ('24','25') ");
				}else{
					hql.append(" and c.category in ('38','39') ");
				}
			}
			
		}
		
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}
		
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		
		
		if(StringUtil.isNotEmpty(classtype)){
			if("null".equals(classtype)){
				hql.append(" and (p.classType is null or p.classType = '')");
			}else{
				hql.append(" and p.classType ='" + classtype + "'");
			}
			
		}
		
		
		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				if(userService.getPermissionsByUser(user_id).getOpe_fk().contains(ope_id)){
					hql.append(" and p.ope_fk = '" + ope_id + "' ");
				}else{
					return null;
				}
			}else{
				String [] opeArray = userService.getPermissionsByUser(user_id).getOpe_fk().trim().split("\\s+");
				ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in " + ope_id);
			}
		}else if(role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				hql.append(" and p.ope_fk = '" + ope_id + "' ");
			}
		}
		
		if(StringUtil.isNotEmpty(score)){
			hql.append(" and p.score = '" + score + "' ");
		}
		
		if(StringUtil.isNotEmpty(bonus)){
			hql.append(" and p.bonus = '" + bonus + "' ");
		}
		hql.append(" order by p.emp_id , p.bonus desc ");
		
		List alllist = hibernateInvoker.find(Map.class, hql.toString());
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    map.put("performance", alllist);
	    listTemp.add(map);
		return listTemp;
	}
	public List getListCompAtibility (String emp_id,String month,String ope_id,String scope,String category,String classtype)  {
		String user_id="sys";
		String userFactory="";
		if(SessionLocal.getUser()!=null){
			userFactory = SessionLocal.getUser().getFactory();
			user_id = SessionLocal.getUser().getUser_id();
		}
			
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null)return null;
		StringBuffer hql = new StringBuffer();
		hql.append(" select new map(p.factory as factory,c.name as name,c.label as label,c.category as category,p.emp_id as emp_id,p.emp_name as emp_name,p.gender as gender, pe.compatibility as compatibility) ");
		hql.append(" from PerformanceMon p join Code c on c.id = p.ope_fk join Performance pe on pe.emp_id = p.emp_id where p.isValid = true ");
		
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
			hql.append(" and pe.month = '" + month + "' ");
		}
		
		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				if(userService.getPermissionsByUser(user_id).getOpe_fk().contains(ope_id)){
					hql.append(" and p.ope_fk = '" + ope_id + "' ");
				}else{
					return null;
				}
			}else{
				String [] opeArray = userService.getPermissionsByUser(user_id).getOpe_fk().trim().split("\\s+");
				ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in " + ope_id );
				
			}
		}else if(role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				hql.append(" and p.ope_fk = '" + ope_id + "' ");
			}
		}
		
		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
				
				if(scope.equals("24")){
					hql.append(" and c.category = '24' ");
				}else if(scope.equals("25")){
					hql.append(" and c.category = '25' ");
				}else if(scope.equals("38")){
					hql.append(" and c.category = '38' ");
				}else if(scope.equals("39")){
					hql.append(" and c.category = '39' ");
				}
			}else{
				if(userFactory.contains("制造")){
					hql.append(" and c.category in ('24','25') ");
				}else{
					hql.append(" and c.category in ('38','39') ");
				}
			}
		}else{
				if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
				
				if(scope.equals("24")){
					hql.append(" and c.category = '24' ");
				}else if(scope.equals("25")){
					hql.append(" and c.category = '25' ");
				}else if(scope.equals("38")){
					hql.append(" and c.category = '38' ");
				}else if(scope.equals("39")){
					hql.append(" and c.category = '39' ");
				}
			}
		}
		
		
		
		
		if(StringUtil.isNotEmpty(classtype)){
			if("null".equals(classtype)){
				hql.append(" and (pe.classType is null or pe.classType = '')");
			}else{
				hql.append(" and pe.classType ='" + classtype + "'");
			}
			
		}
		
		
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}
		
		hql.append(" order by p.emp_id ");

		List alllist = hibernateInvoker.find(Map.class, hql.toString());
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    map.put("compAtibility", alllist);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListDiscipline (String emp_id,String month,String ope_id,String scope,String classtype)  {
		String user_id="sys";
	    if(SessionLocal.getUser()!=null)
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null)return null;
		StringBuffer hql = new StringBuffer();
		hql.append(" select new map(c.label as label,p.emp_id as emp_id,p.emp_name as emp_name, p.discipline as discipline) ");
		hql.append(" from Performance p join Code c on c.id = p.ope_fk where p.isValid = true and c.isValid = true ");
		
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				if(userService.getPermissionsByUser(user_id).getOpe_fk().contains(ope_id)){
					hql.append(" and p.ope_fk = '" + ope_id + "' ");
				}else{
					return null;
				}
			}else{
				String [] opeArray = userService.getPermissionsByUser(user_id).getOpe_fk().trim().split("\\s+");
				ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in " + ope_id );
				
			}
		}else if(role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				hql.append(" and p.ope_fk = '" + ope_id + "' ");
			}
		}
		
		if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
			if(scope.equals("38")){
				hql.append(" and c.category ='38' ");
			}else if(scope.equals("39")){
				hql.append(" and c.category ='39' ");
			}
				}else{
					hql.append(" and c.category in('38','39')");
				}
		
		if(StringUtil.isNotEmpty(classtype)){
			if("null".equals(classtype)){
				hql.append(" and (p.classType is null or p.classType = '')");
			}else{
				hql.append(" and p.classType ='" + classtype + "'");
			}
			
		}
		
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}
		
		hql.append(" order by p.emp_id ");

		List alllist = hibernateInvoker.find(Map.class, hql.toString());
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    map.put("discipline", alllist);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListLearns (String emp_id,String month,String ope_id,String scope,String classtype)  {
		String user_id="sys";
	    if(SessionLocal.getUser()!=null)
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null)return null;
		StringBuffer hql = new StringBuffer();
		hql.append(" select new map(c.label as label,p.emp_id as emp_id,p.emp_name as emp_name, p.learns as learns) ");
		hql.append(" from Performance p join Code c on c.id = p.ope_fk where p.isValid = true and c.isValid = true ");
		
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				if(userService.getPermissionsByUser(user_id).getOpe_fk().contains(ope_id)){
					hql.append(" and p.ope_fk = '" + ope_id + "' ");
				}else{
					return null;
				}
			}else{
				String [] opeArray = userService.getPermissionsByUser(user_id).getOpe_fk().trim().split("\\s+");
				ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in " + ope_id );
				
			}
		}else if(role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				hql.append(" and p.ope_fk = '" + ope_id + "' ");
			}
		}
		
		if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
			if(scope.equals("38")){
				hql.append(" and c.category ='38' ");
			}else if(scope.equals("39")){
				hql.append(" and c.category ='39' ");
			}
				}else{
					hql.append(" and c.category in('38','39')");
				}
		
		if(StringUtil.isNotEmpty(classtype)){
			if("null".equals(classtype)){
				hql.append(" and (p.classType is null or p.classType = '')");
			}else{
				hql.append(" and p.classType ='" + classtype + "'");
			}
			
		}
		
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}
		
		hql.append(" order by p.emp_id ");

		List alllist = hibernateInvoker.find(Map.class, hql.toString());
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    map.put("learns", alllist);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListProfessional (String emp_id,String month,String ope_id,String scope,String classtype)  {
		String user_id="sys";
	    if(SessionLocal.getUser()!=null)
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null)return null;
		StringBuffer hql = new StringBuffer();
		hql.append(" select new map(c.label as label,p.emp_id as emp_id,p.emp_name as emp_name, p.professional as professional) ");
		hql.append(" from Performance p join Code c on c.id = p.ope_fk where p.isValid = true and c.isValid = true ");
		
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		if(!role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				if(userService.getPermissionsByUser(user_id).getOpe_fk().contains(ope_id)){
					hql.append(" and p.ope_fk = '" + ope_id + "' ");
				}else{
					return null;
				}
			}else{
				String [] opeArray = userService.getPermissionsByUser(user_id).getOpe_fk().trim().split("\\s+");
				ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in " + ope_id );
				
			}
		}else if(role.getName().equals("admin")){
			if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
				hql.append(" and p.ope_fk = '" + ope_id + "' ");
			}
		}
		
		if(StringUtil.isNotEmpty(scope) && !scope.equals("0")){
			if(scope.equals("38")){
				hql.append(" and c.category ='38' ");
			}else if(scope.equals("39")){
				hql.append(" and c.category ='39' ");
			}
				}else{
					hql.append(" and c.category in('38','39')");
				}
		
		if(StringUtil.isNotEmpty(classtype)){
			if("null".equals(classtype)){
				hql.append(" and (p.classType is null or p.classType = '')");
			}else{
				hql.append(" and p.classType ='" + classtype + "'");
			}
			
		}
		
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}
		
		hql.append(" order by p.emp_id ");

		List alllist = hibernateInvoker.find(Map.class, hql.toString());
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    map.put("professional", alllist);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	public List getARYPerformance(String emp_id,String month,String ope_id,String score,int pno,int allFlag,String bonus,String grade,String classtype){
		String user_id="sys";
	    if(SessionLocal.getUser()!=null)
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    String role_name = "";
	    if(role != null)
	    	role_name = role.getName();
		StringBuffer hql = new StringBuffer();
		hql.append(" select new map(c.label as label,p.emp_id as emp_id,p.emp_name as emp_name,"
				+ "p.gender as gender,p.bonus as bonus,p.attendance as attendance,"
				+ "p.worktype as worktype,p.compatibility as compatibility,p.workability as workability,"
				+ "p.score as score,p.classType as classtype,p.grade as grade) from Performance p join Code c on c.id = p.ope_fk "
				+ " where p.factory = 'ARY' and p.isValid = true and c.category = '28'");
		
		/*if(!role.getName().equals("admin")){
			if(opForman != null){
				hql.append(" and c.id = '" + opForman.getOpe_fk() + "' ");
			}
		}*/
		
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql.append(" and (p.classType is null or p.classType = '')");
				}else{
					hql.append(" and p.classType ='" + classtype + "'");
				}
				
			}
		}else if("N".equals(role_name)){
			hql.append(" and (p.classType = '' or p.classType is null) ");
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql.append(" and p.classType = 'A' ");
			}else if("B".equals(classtype)){
				hql.append(" and p.classType = 'B' ");
			}else{
				hql.append(" and (p.classType = 'A' or p.classType = 'B') ");
			}
			
		}else{
			hql.append(" and p.classType = '" +  role_name + "'");
		}
		
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}
		
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		if(StringUtil.isNotEmpty(grade)){
			hql.append(" and p.grade = '" + grade + "' ");
		}
		
		
		
		if(StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
			hql.append(" and p.ope_fk = '" + ope_id + "' ");
		}
		
		if(StringUtil.isNotEmpty(score)){
			hql.append(" and p.score = '" + score + "' ");
		}
		
		if(StringUtil.isNotEmpty(bonus)){
			hql.append(" and p.bonus = '" + bonus + "' ");
		}
		hql.append(" order by p.score desc,p.compatibility desc,p.workability desc,p.grade desc,p.attendance desc,p.bonus desc");
		
		List list = hibernateInvoker.find(Map.class,pno,10, hql.toString());
		List alllist = hibernateInvoker.find(Map.class, hql.toString());
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    if(allFlag==1)map.put("performance", alllist);
	    else map.put("performance", list);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListAryAttendenceThisMon (String emp_id,String month,String ope_id,int pno,int allFlag,String classtype) throws ParseException {
		String user_id="sys";
		String userFactory = "";
		String role_name = "";
		if(SessionLocal.getUser()!=null)
		    user_id = SessionLocal.getUser().getUser_id();
		    Role role = userService.getRoleByUser(user_id);
		
		if(role != null)
		   role_name = role.getName();
		if(SessionLocal.getUser()!=null)
			userFactory = SessionLocal.getUser().getFactory();
		String hql = " select new map(c.name as name,c.label as label,p.emp_id as emp_id,"
				+ "p.emp_name as emp_name,p.gender as gender,p.shftHour as shftHour,"
				+ "p.attdHour as attdHour, pe.attendance as attendance, p.classType as classtype) from PerformanceMon p join "
				+ "Code c on c.id = p.ope_fk join Performance pe on p.emp_id = pe.emp_id "
				+ "where p.isValid = true and p.factory = 'ARY' and c.category = '28'";
		if(StringUtil.isNotEmpty(emp_id)){
			hql += " and p.emp_id ='" + emp_id + "'";
		}
		if(StringUtil.isNotEmpty(month)){
			hql += " and p.month ='" + month + "' and pe.month = '" + month + "'";
				}
		if( StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
			hql += " and p.ope_fk ='" + ope_id + "'";
		}
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql += " and (p.classType is null or p.classType = '')";
				}else{
					hql += " and p.classType ='" + classtype + "'";
				}
				
			}
		}else if("N".equals(role_name)){
			hql += " and (p.classType = '' or p.classType is null) ";
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql += " and p.classType = 'A' ";
			}else if("B".equals(classtype)){
				hql += " and p.classType = 'B' ";
			}else{
				hql += " and (p.classType = 'A' or p.classType = 'B') ";
			}
		}else{
			hql += " and p.classType = '" +  role_name + "'";
		}
		List list = hibernateInvoker.find(Map.class,pno,10, hql);
		List alllist = hibernateInvoker.find(Map.class, hql);
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    if(allFlag==1)map.put("attendence", alllist);
	    else map.put("attendence", list);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListWorkType (String emp_id,String month,String ope_id,int pno,int allFlag,String classtype) throws ParseException {
		 String userFactory="";
		 String user_id = "";
		 String role_name = "";
			if(SessionLocal.getUser()!=null)
			    user_id = SessionLocal.getUser().getUser_id();
			    Role role = userService.getRoleByUser(user_id);
			
			if(role != null)
			   role_name = role.getName();
		if(SessionLocal.getUser()!=null)
			userFactory = SessionLocal.getUser().getFactory();
		String hql = " select new map(c.name as name,c.label as label,p.emp_id as emp_id,"
				+ "p.emp_name as emp_name,p.gender as gender,c1.name as worktype_name,"
				+ "pe.worktype as worktype) from PerformanceMon p join "
				+ "Code c on c.id = p.ope_fk join Code c1 on c1.id = p.workType join Performance pe on p.emp_id = pe.emp_id "
				+ "where p.isValid = true and p.factory = 'ARY' and c.category = '28'";
		if(StringUtil.isNotEmpty(emp_id)){
			hql += " and p.emp_id ='" + emp_id + "'";
		}
		if(StringUtil.isNotEmpty(month)){
			hql += " and p.month ='" + month + "'";
			hql += " and pe.month ='" + month + "'";
				}
		if( StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
			hql += " and p.ope_fk ='" + ope_id + "'";
		}
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql += " and (p.classType is null or p.classType = '')";
				}else{
					hql += " and p.classType ='" + classtype + "'";
				}
				
			}
		}else if("N".equals(role_name)){
			hql += " and (p.classType = '' or p.classType is null) ";
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql += " and p.classType = 'A' ";
			}else if("B".equals(classtype)){
				hql += " and p.classType = 'B' ";
			}else{
				hql += " and (p.classType = 'A' or p.classType = 'B') ";
			}
		}else{
			hql += " and p.classType = '" +  role_name + "'";
		}
		List list = hibernateInvoker.find(Map.class,pno,10, hql);
		List alllist = hibernateInvoker.find(Map.class, hql);
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    if(allFlag==1)map.put("worktype", alllist);
	    else map.put("worktype", list);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListWorkAbility (String emp_id,String month,String ope_id,int pno,int allFlag,String classtype) throws ParseException {
		 String userFactory="";
		 String user_id = "";
		 String role_name = "";
			if(SessionLocal.getUser()!=null)
			    user_id = SessionLocal.getUser().getUser_id();
			    Role role = userService.getRoleByUser(user_id);
			
			if(role != null)
			   role_name = role.getName();
		if(SessionLocal.getUser()!=null)
			userFactory = SessionLocal.getUser().getFactory();
		String hql = " select new map(c.name as name,c.label as label,p.emp_id as emp_id,"
				+ "p.emp_name as emp_name,p.gender as gender,p.workAbility as workAbility,"
				+ "p.attdHour as attdHour) from PerformanceMon p join "
				+ "Code c on c.id = p.ope_fk "
				+ "where p.isValid = true and p.factory = 'ARY' and c.category = '28'";
		if(StringUtil.isNotEmpty(emp_id)){
			hql += " and p.emp_id ='" + emp_id + "'";
		}
		if(StringUtil.isNotEmpty(month)){
			hql += " and p.month ='" + month + "'";
				}
		if( StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
			hql += " and p.ope_fk ='" + ope_id + "'";
		}
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql += " and (p.classType is null or p.classType = '')";
				}else{
					hql += " and p.classType ='" + classtype + "'";
				}
				
			}
		}else if("N".equals(role_name)){
			hql += " and (p.classType = '' or p.classType is null) ";
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql += " and p.classType = 'A' ";
			}else if("B".equals(classtype)){
				hql += " and p.classType = 'B' ";
			}else{
				hql += " and (p.classType = 'A' or p.classType = 'B') ";
			}
		}else{
			hql += " and p.classType = '" +  role_name + "'";
		}
		List list = hibernateInvoker.find(Map.class,pno,10, hql);
		List alllist = hibernateInvoker.find(Map.class, hql);
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    if(allFlag==1)map.put("workability", alllist);
	    else map.put("workability", list);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListCompAtibility (String emp_id,String month,String ope_id,int pno,int allFlag,String classtype) throws ParseException {
		 String userFactory="";
		 String user_id = "";
		 String role_name = "";
			if(SessionLocal.getUser()!=null)
			    user_id = SessionLocal.getUser().getUser_id();
			    Role role = userService.getRoleByUser(user_id);
			
			if(role != null)
			   role_name = role.getName();
		if(SessionLocal.getUser()!=null)
			userFactory = SessionLocal.getUser().getFactory();
		String hql = " select new map(c.name as name,c.label as label,p.emp_id as emp_id,"
				+ "p.emp_name as emp_name,p.gender as gender,p.compatibility as compatibility) "
				+ "from PerformanceMon p join Code c on c.id = p.ope_fk "
				+ "where p.isValid = true and p.factory = 'ARY' and c.category = '28'";
		if(StringUtil.isNotEmpty(emp_id)){
			hql += " and p.emp_id ='" + emp_id + "'";
		}
		if(StringUtil.isNotEmpty(month)){
			hql += " and p.month ='" + month + "'";
				}
		if( StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
			hql += " and p.ope_fk ='" + ope_id + "'";
		}
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql += " and (p.classType is null or p.classType = '')";
				}else{
					hql += " and p.classType ='" + classtype + "'";
				}
				
			}
		}else if("N".equals(role_name)){
			hql += " and (p.classType = '' or p.classType is null) ";
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql += " and p.classType = 'A' ";
			}else if("B".equals(classtype)){
				hql += " and p.classType = 'B' ";
			}else{
				hql += " and (p.classType = 'A' or p.classType = 'B') ";
			}
		}else{
			hql += " and p.classType = '" +  role_name + "'";
		}
		List list = hibernateInvoker.find(Map.class,pno,10, hql);
		List alllist = hibernateInvoker.find(Map.class, hql);
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    if(allFlag==1)map.put("compAtibility", alllist);
	    else map.put("compAtibility", list);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	public List getListMajorLoss (String from_date,String to_date,String type,String ope_id,int pno,int allFlag,String classtype) throws ParseException {
		 String userFactory="";
		 String user_id = "";
		 String role_name = "";
			if(SessionLocal.getUser()!=null)
			    user_id = SessionLocal.getUser().getUser_id();
			    Role role = userService.getRoleByUser(user_id);
			
			if(role != null)
			   role_name = role.getName();
		if(SessionLocal.getUser()!=null)
			userFactory = SessionLocal.getUser().getFactory();
		String hql = "select new map(pd.id as id,pd.fab_date as fab_date,pd.category as category,c.label as label,"
					+ " pd.emp_id as emp_id,pd.emp_name as emp_name,pd.reason as reason) from PerformanceDay pd "
					+ " join Code c on c.id = pd.ope_fk where pd.isValid = true and c.isValid = true and pd.factory = 'ARY' and c.category = '28'";
		if(StringUtil.isNotEmpty(from_date)){
			hql += " and pd.fab_date >='" + from_date + "'";
		}
		
		if(StringUtil.isNotEmpty(to_date)){
			hql += " and pd.fab_date <='" + to_date + "'";
		}
		
		if(StringUtil.isNotEmpty(type)){
			hql += " and pd.type ='" + type + "'";
		}
		
		if( StringUtil.isNotEmpty(ope_id) && !ope_id.equals("0")){
			hql += " and pd.ope_fk ='" + ope_id + "'";
		}
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql += " and (p.classType is null or p.classType = '')";
				}else{
					hql += " and p.classType ='" + classtype + "'";
				}
				
			}
		}else if("N".equals(role_name)){
			hql += " and (p.classType = '' or p.classType is null) ";
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql += " and p.classType = 'A' ";
			}else if("B".equals(classtype)){
				hql += " and p.classType = 'B' ";
			}else{
				hql += " and (p.classType = 'A' or p.classType = 'B') ";
			}
		}else{
			hql += " and p.classType = '" +  role_name + "'";
		}
		List list = hibernateInvoker.find(Map.class,pno,10, hql);
		List alllist = hibernateInvoker.find(Map.class, hql);
	    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    if(allFlag==1)map.put("majorLoss", alllist);
	    else map.put("majorLoss", list);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	
	public double getAryAverageBouns(String month, Long ope_fk,String bonus,String score,String emp_id,String grade,String classtype) {
		String user_id="sys";
		String role_name = "";
		if(SessionLocal.getUser() != null){
			user_id = SessionLocal.getUser().getUser_id();
		}
	    Role role = userService.getRoleByUser(user_id);
	    if(role != null){
	    	role_name = role.getName();
	    }
	   // String operations = userService.getPermissionsByUser(user_id) == null ? null:userService.getPermissionsByUser(user_id).getOpe_fk();
		StringBuilder hql = new StringBuilder();
		hql.append(" select sum(p.bonus)/count(*) as average from Performance p where p.isValid is true "
				+ "and p.factory = 'ARY'");
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		if(StringUtil.isNotEmpty(bonus)){
			hql.append(" and p.bonus = '" + bonus + "' ");
		}
		
		if(StringUtil.isNotEmpty(score)){
			hql.append(" and p.score = '" + score + "' ");
		}

		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}

		if(StringUtil.isNotEmpty(grade)){
			hql.append(" and p.classType = '" + grade + "' ");
		}
		
		
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql.append(" and (p.classType is null or p.classType = '')");
				}else{
					hql.append(" and p.classType ='" + classtype + "'");
				}
				
			}
		}else if("N".equals(role_name)){
			hql.append(" and (p.classType = '' or p.classType is null) ");
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql.append(" and p.classType = 'A' ");
			}else if("B".equals(classtype)){
				hql.append(" and p.classType = 'B' ");
			}else{
				hql.append(" and (p.classType = 'A' or p.classType = 'B') ");
			}
		}else{
			hql.append(" and p.classType = '" +  role_name + "'");
		}
		
		
	/*	if(!"admin".equals(role.getName())){
			
			if(!operations.contains(ope_fk + "")){
				return 0.0;
			}
			if((ope_fk != 0 && operations.contains(ope_fk + ""))){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			if(ope_fk == 0){
				String [] opeArray = operations.trim().split("\\s+");
				String ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in "  + ope_id);
			}
		
		}else{
			if(ope_fk != 0){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
		}*/
		
		
		
		return hibernateInvoker.findUnique(Double.class, hql.toString())==null?0:hibernateInvoker.findUnique(Double.class, hql.toString());
	}
	
	public double getAryAllBonuses(String month, Long ope_fk,String bonus,String score,String emp_id,String grade,String classtype) {
		String user_id="sys";
		String role_name = "";
		if(SessionLocal.getUser() != null){
			user_id = SessionLocal.getUser().getUser_id();
		}
	    Role role = userService.getRoleByUser(user_id);
	    if(role != null){
	    	role_name = role.getName();
	    }
	    String operations = userService.getPermissionsByUser(user_id) == null ? null:userService.getPermissionsByUser(user_id).getOpe_fk();
	    OperationForeman opForman = userService.getOpeIDByUser(user_id);
		StringBuilder hql = new StringBuilder();
		hql.append(" from Performance p where p.isValid is true and p.factory ='ARY'");
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		if(StringUtil.isNotEmpty(bonus)){
			hql.append(" and p.bonus = '" + bonus + "' ");
		}
		
		if(StringUtil.isNotEmpty(score)){
			hql.append(" and p.score = '" + score + "' ");
		}

		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}

		if(StringUtil.isNotEmpty(grade)){
			hql.append(" and p.classType = '" + grade + "' ");
		}
		
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql.append(" and (p.classType is null or p.classType = '')");
				}else{
					hql.append(" and p.classType ='" + classtype + "'");
				}
				
			}
		}else if("N".equals(role_name)){
			hql.append(" and (p.classType = '' or p.classType is null) ");
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql.append(" and p.classType = 'A' ");
			}else if("B".equals(classtype)){
				hql.append(" and p.classType = 'B' ");
			}else{
				hql.append(" and (p.classType = 'A' or p.classType = 'B') ");
			}
		}else{
			hql.append(" and p.classType = '" +  role_name + "'");
		}
		
		
		/*if(!role.getName().equals("admin")){
			
			if(!operations.contains(ope_fk + "")){
				return 0.0;
			}
			if((ope_fk != 0 && operations.contains(ope_fk + ""))){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			if(ope_fk == 0){
				String [] opeArray = operations.trim().split("\\s+");
				String ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in "  + ope_id);
			}
		
		}else{
			if(ope_fk != 0){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
		}*/
		List<Performance> list = hibernateInvoker.find(Performance.class, hql.toString());
		return list.size() * 100.0;
	}
	
	public double getAryActualBonus(String month, Long ope_fk,String bonus,String score,String emp_id,String grade,String classtype) {
		String user_id="sys";
		String role_name = "";
		if(SessionLocal.getUser() != null){
			user_id = SessionLocal.getUser().getUser_id();
		}
	    Role role = userService.getRoleByUser(user_id);
	    if(role != null){
	    	role_name = role.getName();
	    }
	    String operations = userService.getPermissionsByUser(user_id) == null ? null:userService.getPermissionsByUser(user_id).getOpe_fk();
	    OperationForeman opForman = userService.getOpeIDByUser(user_id);
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(p.bonus) as allbonus from Performance p where p.isValid is true and p.factory = 'ARY'");
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");
		}
		
		if(StringUtil.isNotEmpty(bonus)){
			hql.append(" and p.bonus = '" + bonus + "' ");
		}
		
		if(StringUtil.isNotEmpty(score)){
			hql.append(" and p.score = '" + score + "' ");
		}

		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and p.emp_id = '" + emp_id + "' ");
		}

		if(StringUtil.isNotEmpty(grade)){
			hql.append(" and p.classType = '" + grade + "' ");
		}
		
		if("admin".equals(role_name)){
			if(StringUtil.isNotEmpty(classtype)){
				if("null".equals(classtype)){
					hql.append(" and (p.classType is null or p.classType = '')");
				}else{
					hql.append(" and p.classType ='" + classtype + "'");
				}
				
			}
		}else if("N".equals(role_name)){
			hql.append(" and (p.classType = '' or p.classType is null) ");
		}else if("AB".equals(role_name)){
			if("A".equals(classtype)){
				hql.append(" and p.classType = 'A' ");
			}else if("B".equals(classtype)){
				hql.append(" and p.classType = 'B' ");
			}else{
				hql.append(" and (p.classType = 'A' or p.classType = 'B') ");
			}
		}else{
			hql.append(" and p.classType = '" +  role_name + "'");
		}
		
		
		/*if(!role.getName().equals("admin")){

			if(!operations.contains(ope_fk + "")){
				return 0.0;
			}
			if((ope_fk != 0 && operations.contains(ope_fk + ""))){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
			if(ope_fk == 0){
				String [] opeArray = operations.trim().split("\\s+");
				String ope_id = "(";
				for(int j = 0; j < opeArray.length; j ++){
					if(j == opeArray.length - 1){
						ope_id = ope_id + "'" + opeArray[j] + "')";
					}else{
						ope_id = ope_id + "'" + opeArray[j] + "',";
					}
				}
				hql.append(" and p.ope_fk in "  + ope_id);
			}
		
		}else{
			if(ope_fk != 0){
				hql.append(" and p.ope_fk = '" + ope_fk + "' ");
			}
		}*/
		return hibernateInvoker.findUnique(Double.class, hql.toString())==null?0:hibernateInvoker.findUnique(Double.class, hql.toString());
	}
	
	
	
	
    @Transactional
 	public String updateAryWorkability(String month,String emp_id,String user_id, String workability) {
     	String result="";
     	PerformanceMon objPerformanceMon = performanceMonManager.findUnique("from PerformanceMon p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
     	if(objPerformanceMon==null){
     		result="1";
     	}
     	else{
     		objPerformanceMon.setWorkAbility(Double.parseDouble(workability));
     		performanceMonManager.save(objPerformanceMon);
     		result="0";
     	}
 		return result;
     	
 	}
	
	
 	
    @Transactional
 	public String updateAryCompatibility(String month,String emp_id,String user_id, String compatibility) {
     	String result="";
     	PerformanceMon objPerformanceMon = performanceMonManager.findUnique("from PerformanceMon p where p.isValid is true and p.emp_id = ?0 and p.month=?1", emp_id,month);
     	if(objPerformanceMon==null){
     		result="1";
     	}
     	else{
     		objPerformanceMon.setCompatibility(Double.parseDouble(compatibility));
     		performanceMonManager.save(objPerformanceMon);
     		result="0";
     	}
 		return result;
     	
 	}
 	
 	
	public List getOperation (long category) throws ParseException {
		String hql = " from Code co where co.isValid = true";
		if(category != 0){
			hql += " and co.category= ?0 ";
			List<Code> operationList = hibernateInvoker.find(Code.class, hql,category);
			return operationList;
		}else{
			hql += " and co.category= 276 or co.category= 275";
			List<Code> operationList = hibernateInvoker.find(Code.class, hql);
			return operationList;
		}
		
		
		
		}

	public void deleteDustfreeBymon(String month) {
        String hql ="select d.id from Dustfree d  where d.isValid = true and d.month = ?0";
	    List<Long> list = hibernateInvoker.find(Long.class, hql,month);
	    Long[] ids = (Long[])list.toArray(new Long[list.size()]);
	    dustfreeManager.delete(ids);
	}

	public List listdustfree(int allFlag, int pno, String factory, String emp_id, Long[] ope_fk, String classType,String month,String condition) {
        StringBuilder hql = 
        		 new StringBuilder("select new map(p.month as month,p.factory as factory,p.emp_id as emp_id,p.emp_name as emp_name,p.department_id as department_id,p.classType as classType,p.department_name as department_name,")
        		.append(" p.dateOfRegister as dateOfRegister,c.name as ope_id,c.label as ope_name,d.hour as hour,d.amount as amount,d.memo as memo) from PerformanceMon p")
        		.append(" left join Dustfree d on p.month= d.month and p.factory = d.factory and p.emp_id=d.emp_id and p.isValid is true and d.isValid is true")
                .append(" left join Code c on p.ope_fk=c.id")
                .append(" where p.isValid is true ");
        
        int paramPosition = 0;
        List<Object> params = new ArrayList<>();
        
        if (StringUtil.isNotEmpty(factory)) {
        	if(factory.contains("M")){
            	if(factory.equals("LCM")){
                    hql.append(" and p.factory in ('M1','M2','LCM') ");
                	}else{
                		if(factory.contains("M1")){
                			hql.append(" and p.factory in ('M1','LCM') ");
                		}else if(factory.contains("M2")){
                			hql.append(" and p.factory in ('M2','LCM') ");
                		}
                	}
        	}else{
            		
	            	String placeholder = "?" + paramPosition++;
	                hql.append(" and p.factory = ").append(placeholder);
	                params.add(factory);
	                
	                if(factory.equals("CEL")) //cel厂  ：制造一课、制造二课、生产支援课、训练课
	                	hql.append(" and p.department_id in ( '10000042','10000219','10001141','10000043') ");
	                
//	                if(factory.equals("ARY")) //ary厂  ：生产支援课、生产课
//	                    hql.append(" and p.department_id in ( '10000026','10000025') ");
	            }
        }
        
        if (StringUtil.isNotEmpty(month)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and p.month = ").append(placeholder);
            params.add(month);
        }
        
        if (ope_fk!=null && ope_fk.length>0) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and p.ope_fk in ( ").append(placeholder).append(")");
            params.add(ope_fk);
        }
        
        if (StringUtil.isNotEmpty(emp_id)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and p.emp_id like ").append(placeholder);
            params.add(HqlHelper.like(emp_id));
        }
        if (StringUtil.isNotEmpty(classType)) {
            String placeholder = "?" + paramPosition++;
            hql.append(" and p.classType = ").append(placeholder);
            params.add(classType);
        }
        String queryString = hql.toString();
        List list = hibernateInvoker.find(Map.class, pno,10,queryString, params.toArray());
        List alllist = hibernateInvoker.find(Map.class,queryString, params.toArray());
	    	List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    	Map<String, Object> map = new HashMap<String, Object>();
		String user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
        if(role!=null) {
        		map.put("roleName",role.getName());
        }
	    	map.put("allTotal", alllist.size());
	    	if(allFlag==1)map.put("dustfree", alllist);
	    	else map.put("dustfree", list);
	    	listTemp.add(map);
		return listTemp;
	}

	
	
	
	
	
	
	public MajorAddition getMajorAddition(String month, String emp_id) {
		String hql = "select u from MajorAddition u  where u.isValid=true and u.month=?0 and u.emp_id=?1 ";
		return majorAdditionManager.findUnique(hql, month,emp_id);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List listMajorAddition(int allFlag, int pno, String factory, String emp_id, Long[] ope_fk,Double amount,String classType, String month,String condition) {
       
		if (StringUtil.isEmpty(month)) {
			month = "9999-09";
        }
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT p.MONTH AS MONTH, p.factory AS factory, p.emp_id AS emp_id, p.emp_name AS emp_name, c.NAME AS ope_id, ");
		sql.append(" c.label AS ope_name,p.exam_name AS exam_name,p.LEVEL AS level,p.effective_date AS effective_date,p.seniority AS seniority,");
		sql.append(" a.classType AS classType,p.amount AS amount,p.EFFECTIVE_Date AS effevtive_date,p.memo AS memo FROM(SELECT ");
		sql.append(" MONTH,factory,emp_id,emp_name,exam_name,LEVEL,seniority,amount,EFFECTIVE_Date,ope_fk,memo,is_Valid FROM dl_m_majoraddition ");
		sql.append(" WHERE is_Valid IS TRUE AND MONTH = '" + month + "') p INNER JOIN (SELECT classType,MONTH,emp_id,factory,is_Valid FROM ");
		sql.append(" dl_m_performancemon WHERE is_Valid IS TRUE AND MONTH = '" + month + "' ) a ON p. MONTH = a. MONTH ");
		sql.append(" AND p.emp_id = a.emp_id AND a.is_Valid IS TRUE LEFT JOIN sys_o_code c ON p.ope_fk = c.id WHERE p.is_Valid IS TRUE  ");
        List<Object> params = new ArrayList<>();
        
        
        if (StringUtil.isNotEmpty(factory)) {
        	if(factory.contains("M")){
            	if(factory.equals("LCM")){
            		sql.append(" and p.factory in ('M1','M2','LCM') ");
                	}else{
                		if(factory.contains("M1")){
                			sql.append(" and p.factory in ('M1','LCM') ");
                		}else if(factory.contains("M2")){
                			sql.append(" and p.factory in ('M2','LCM') ");
                		}
                	}
        	}else{
	            	sql.append(" and p.factory = '" + factory + "'");
	            }
        }
        
        
        if (ope_fk!=null && ope_fk.length>0) {


        	
        	
        	String opeid = "";
			int count = 1;
			opeid += " (";
			for(long s : ope_fk){
				if(count == ope_fk.length){
					opeid += "'" + s + "')";
				}else{
					opeid += "'" + s + "',";
					count ++;
				}
			}
			sql.append(" and p.ope_fk in " + opeid + "");
        	
        	
        	
        }
        
        if (StringUtil.isNotEmpty(emp_id)) {
            sql.append(" and p.emp_id like '" + emp_id + "'");
        }
        if (amount !=null) {
            sql.append(" and p.amount = '" + amount + "'");
        }
        if (StringUtil.isNotEmpty(classType)) {
            sql.append(" and a.classType = '" +  classType + "'");
        }
        String queryString = sql.toString();
        PMSDBUtil db = new PMSDBUtil();
        List alllist = db.excuteQuery(queryString, params.toArray());
        List list = new ArrayList();
        if(allFlag != 1){
        	int from = 0,to = 9;
        	from = from + 10 * (pno - 1);
        	to = to + 10 * (pno - 1);
        	if(alllist.size() <= to)to = alllist.size()-1;
        	for(int i = from; i <= to; i ++){
        		list.add(alllist.get(i));
        	}
        }
        
    	List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
    	Map<String, Object> map = new HashMap<String, Object>();
    	map.put("allTotal", alllist.size());
    	if(allFlag==1)map.put("majorAddition", alllist);
    	else map.put("majorAddition", list);
    	listTemp.add(map);
		return listTemp;
	}
	
	@Transactional
	public int addPermissions (String emp_id,String scope,String[] ope_id,String emp_name) {
		Role role = userService.getRoleByUser(SessionLocal.getUser().getUser_id());
		if(!role.getName().equals("admin")) return 0;
		Permissions ps =  hibernateInvoker.findUnique(Permissions.class, "from Permissions ps where ps.user_id = '" + emp_id + "' and ps.isValid = true");
		if(ps != null){
			for(String o : ope_id){
				if(!ps.getOpe_fk().contains(o)){
					ps.setOpe_fk(ps.getOpe_fk() + " " + o);
				}
			}
			permissionsManager.save(ps);
			return 1;
		}
		
		ps = new Permissions(); 
		ps.setUser_id(emp_id);
		ps.setUser_name(emp_name);
		String ope_ID = "";
		for(String o : ope_id){
			ope_ID = ope_ID + " " + o;
		}
		ps.setOpe_fk(ope_ID);
		permissionsManager.save(ps);
		return 1;
	}
	
	
	
	
	@Transactional
	public int addUser (String emp_id,String emp_name,String scope) {
		Role role = userService.getRoleByUser(SessionLocal.getUser().getUser_id());
		if(!role.getName().equals("admin")) return 0;
		User user =  hibernateInvoker.findUnique(User.class, "from User u where u.user_id = '" + emp_id + "' and u.isValid = true");
		if(user == null){
			user = new User();
			if("LCM".equals(SessionLocal.getUser().getFactory())){
				if(scope.equals("24")){
					user.setFactory("M1制造部");
				}else if(scope.equals("25")){
					user.setFactory("M2制造部");
				}else if(scope.equals("38")){
					user.setFactory("M1工程部");
				}else if(scope.equals("39")){
					user.setFactory("M2工程部");
				}
				
			}else{
				user.setFactory(SessionLocal.getUser().getFactory());
			}
			user.setPassword("123");
			user.setUser_id(emp_id);
			user.setUser_name(emp_name);
			userManager.save(user);
			UserRole ur = new UserRole();
			ur.setUser_fk(user.getId());
			ur.setRole_fk(12L);
			userRoleManager.save(ur);
		}else{
			return 2;
		}
		return 1;
	}
	
	
	
	
	public List getPermissions(String emp_id,int pno,int allFlag) {
		StringBuilder hql = new StringBuilder();
		String operations = "";
		
		if(SessionLocal.getUser().getFactory().contains("M")){
			hql.append("select ps from Permissions ps left join User u on u.user_id = ps.user_id where ps.isValid = true and u.isValid = true and u.factory like '%M%'");
		}else{
			hql.append("select ps from Permissions ps left join User u on u.user_id = ps.user_id where ps.isValid = true and u.isValid = true and u.factory = '" + SessionLocal.getUser().getFactory() + "'");
		}
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and ps.user_id ='" + emp_id + "' order by ps.createTime desc");
		}else{
			hql.append(" order by ps.createTime desc");
		}
		
			List list = hibernateInvoker.find(Map.class,pno,10, hql.toString());
			List alllist = hibernateInvoker.find(Map.class, hql.toString());
			if(allFlag ==1){
				for(Object per : alllist){
					Permissions ps = (Permissions)per;
					if(ps != null){
						String operation = ps.getOpe_fk();
						String [] opeArray = operation.trim().split("\\s+");
						for(int i = 0; i < opeArray.length; i ++){
							Code c = hibernateInvoker.findUnique(Code.class, "from Code c where c.isValid = true and c.id = '" + opeArray[i] + "'");
							if(i == opeArray.length - 1){
								operations = operations + c.getLabel();
							}else{
								operations = operations + c.getLabel() + ",";
							}
							
						}
						ps.setOpe_fk(operations);
						operations = "";
					}
					
				}
			}else{
				for(Object per : list){
					Permissions ps = (Permissions)per;
					if(ps != null){
						String operation = ps.getOpe_fk();
						String [] opeArray = operation.trim().split("\\s+");
						for(int i = 0; i < opeArray.length; i ++){
							Code c = hibernateInvoker.findUnique(Code.class, "from Code c where c.isValid = true and c.id = '" + opeArray[i] + "'");
							if(i == opeArray.length - 1){
								operations = operations + c.getLabel();
							}else{
								operations = operations + c.getLabel() + ",";
							}
							
						}
						ps.setOpe_fk(operations);
						operations = "";
					}
					
				}
			}
			
			
			
			
			
			
			
			
		    List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
		    Map<String, Object> map = new HashMap<String, Object>();
		    map.put("allTotal", alllist.size());
		    if(allFlag==1)map.put("Permissions", alllist);
		    else map.put("Permissions", list);
		    listTemp.add(map);
			return listTemp;
	}
	
	
	
	
	@SuppressWarnings({ "rawtypes" })
	public List<Map<String, Object>> getUsers(String emp_id,int pno,int allFlag) {
		String factory = "";StringBuilder hql = new StringBuilder();
		if(SessionLocal.getUser()!=null)
			factory = SessionLocal.getUser().getFactory();
		
		if(StringUtil.isNotEmpty(factory)){
			if(factory.contains("M")){
				hql = new StringBuilder("select new map(u.user_id as user_id,u.user_name as user_name,u.factory as factory) from User u where u.isValid = true and u.factory like '%M%' and u.factory != 'LCM' ");
			}else{
			hql = new StringBuilder("select new map(u.user_id as user_id,u.user_name as user_name,u.factory as factory) from User u where u.isValid = true and u.factory like '%" + factory + "%' and u.factory != 'LCM' ");
		}
			}
		
		//
		
		if(StringUtil.isNotEmpty(emp_id)){
			hql.append(" and u.user_id = '" + emp_id + "'");
		}
		
		hql.append(" order by u.createTime desc ");
		List<Map> list = hibernateInvoker.find(Map.class,pno,10, hql.toString());
		List<Map> alllist = hibernateInvoker.find(Map.class, hql.toString());
		List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    Map<String, Object> map = new HashMap<String, Object>();
	    map.put("allTotal", alllist.size());
	    if(allFlag==1)map.put("Users", alllist);
	    else map.put("Users", list);
	    listTemp.add(map);
		return listTemp;
	}
	
	
	
	
	@Transactional
	public int deletePermissions(String emp_id) {
	    String hql =" from Permissions ps where ps.user_id = '" + emp_id + "' and ps.isValid = true";
	    Permissions ps = hibernateInvoker.findUnique(Permissions.class, hql);
	    if(ps != null){
	    	ps.setIsValid(false);
	    	return 1;
	    }else{
	    	return 0;
	    }
	    /*String opeartions = "";
	    String[] operationsArray = ps.getOpe_fk().trim().split("\\s+");
	    if(operationsArray.length == 1){
	    	ps.setIsValid(false);
	    	return 1;
	    }
	    List<String> list = new ArrayList<String>(); 
	    for(String ope_fk : operationsArray){
	    	list.add(ope_fk);
	    }
	    for(String ope_fk : list){
	    	Code c = hibernateInvoker.findUnique(Code.class, " from Code c where c.id = '" + ope_fk + "' ");
	    	if(c != null){
	    		if(ope_id.equals(c.getName())){
	    			list.remove(ope_fk);
	    		}
	    	}
	    }
	    for(String ope_fk : list){
	    	opeartions = opeartions + " " + ope_fk;
 	    }
	    
	    ps.setOpe_fk(opeartions);
	    return 1;*/
	    
	}
	
	
	
	
	@Transactional
	public int deleteUser(String emp_id) {
	    String hql =" from User u where u.user_id = '" + emp_id + "' and u.isValid = true";
	    User u = hibernateInvoker.findUnique(User.class, hql);
	    if(u != null){
	    	u.setIsValid(false);
	    	return 1;
	    }else{
	    	return 0;
	    }
	}
	
	
	
	@Transactional
	public int changePassword(String oldpassword,String newpassword) {
		String emp_id = SessionLocal.getUser().getUser_id();
	    String hql =" from User u where u.user_id = '" + emp_id + "' and u.isValid = true";
	    User u = hibernateInvoker.findUnique(User.class, hql);
	    if(u != null){
	    	if(!oldpassword.equals(u.getPassword())){
	    		return 0;
	    	}
	    	u.setPassword(newpassword);
	    	return 1;
	    }else{
	    	return 0;
	    }
	}
	
	
	
	@Transactional
	public String isUserExist(String emp_id) {
		String factory = SessionLocal.getUser().getFactory();
		 String hql =" from User u where u.user_id = '" + emp_id + "' and u.isValid = true";
		if(factory.contains("M")){
			hql = hql + " and u.factory like '%M%' ";
		}else{
			hql = hql + " and u.factory = '" + factory + "'  ";
		}
	   
	    User u = hibernateInvoker.findUnique(User.class, hql);
	    if(u != null){
	    	return u.getUser_name();
	    }else{
	    	return null;
	    }
	}
	
	

	public int getadmin () {
		Role role = userService.getRoleByUser(SessionLocal.getUser().getUser_id());
		if(!role.getName().equals("admin")){
			return 0;
		}else{
			return 1;
		}
			
		
	}
	
	@Transactional
	public String updateMajorAmount(String month, String emp_id, Double amount) {
    	String result="";
    	String hql = "select u from MajorAddition u  where u.isValid=true and u.month=?0 and u.emp_id=?1 ";
    	MajorAddition objMajorAddition = majorAdditionManager.findUnique(hql, month,emp_id);
    	if(objMajorAddition==null){
    		result="1";
    	}
    	else{
    		objMajorAddition.setAmount(amount);
    		majorAdditionManager.save(objMajorAddition);
    		result="0";
    	}
		return result;
	}
	@Transactional
	public String updateDustfree(String month, String emp_id, Double amount) {
	String user_id = SessionLocal.getUser().getUser_id();
	String result="";
    	String hql = "select u from Dustfree u  where u.isValid=true and u.month=?0 and u.emp_id=?1 ";
    	Dustfree objDustfree = dustfreeManager.findUnique(hql, month,emp_id);
    	if(objDustfree==null){
    		result="1";
    	}
    	else{
    		objDustfree.setAmount(amount);
    		objDustfree.setMemo(objDustfree.getMemo()+"<br>"+DateUtil.formatDateTime(new Date())+":"+user_id+"更新了数据;");
    		dustfreeManager.save(objDustfree);
    		result="0";
    	}
		return result;
	}
	
	 public List getWHPerformance(int allFlag,int pno,String emp_id,String month,Double bonus,Double score,String dept_ID){
	    	
	        String user_id="sys";
	        if(SessionLocal.getUser()!=null)
	        user_id = SessionLocal.getUser().getUser_id();
	        
	        Role role = userService.getRoleByUser(user_id);
	        if(role == null) return null;
	        String role_name = role.getName();
	        

	        
	        StringBuilder hql = new StringBuilder();
	        hql.append(" select new map(pm.department_name as department_name,p.emp_id as emp_id,p.emp_name as emp_name, ");
	        hql.append(" p.dailywork as dailywork,p.attendance as attendance,p.discipline as discipline, ");
	        hql.append(" p.safetyatwork as safetyatwork,p.audit as audit,p.workhandover as workhandover, ");
	        hql.append(" p.abnormal as abnormal,p.score as score,p.composite as composite,p.bonus as bonus,p.memo as memo) ");
	        hql.append(" from Performance p left join PerformanceMon pm ");
	        hql.append(" on p.emp_id = pm.emp_id and p.month = pm.month where p.isValid = true and pm.isValid = true ");
	     
	       
	       if("M1W".equals(role_name)){
	    	   hql.append(" and pm.department_id = '10000380' ");
	       }else{
	    	   hql.append(" and (pm.department_id = '10000379' or pm.department_id = '10000434') ");
	       }
	        
	       if(StringUtil.isNotEmpty(emp_id)){
	    	   hql.append(" and p.emp_id = '" + emp_id + "' ");
	       }
	        
	       	if(StringUtil.isNotEmpty(month)){
				hql.append(" and p.month = '" + month + "' ");	
			}
			
			if(StringUtil.isNotEmpty(dept_ID)){
				if("10000380".equals(dept_ID)){
					hql.append(" and pm.department_id = '" + dept_ID + "' ");
				}else{
					hql.append(" and (pm.department_id = '10000434' or pm.department_id = '10000379') ");
				}
				
			}
			
			if (bonus!=null){
		        hql.append(" and p.bonus = '" + bonus + "' ");
			}
			
			if (score!=null){
				 hql.append(" and p.score = '" + score + "' ");
			}
			
			 hql.append(" order by p.groups desc,p.emp_id desc");
	        
	        List list = hibernateInvoker.find(Map.class, pno,10, hql.toString());
	        List alllist = hibernateInvoker.find(Map.class, hql.toString());
	    	List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
	    	Map<String, Object> map = new HashMap<String, Object>();
	    	map.put("allTotal", alllist.size());
	    	if(allFlag==1)map.put("performance", alllist);
	    	else map.put("performance", list);
	    	listTemp.add(map);
			return listTemp;
	    } 
	 
	 
	@Transactional
	public String saveWHPerformance(List<Map<String, String>> list) {
			String result = "0";
			 
		   for(int i = 0; i < list.size(); i++) {
			Map<String, String> jsonObject = list.get(i);
		    
		    String month = jsonObject.get("month");
		    String employee_id = jsonObject.get("employee_id");
		    String employee_name = jsonObject.get("employee_name");
			String dailywork = jsonObject.get("dailywork");
			String discipline = jsonObject.get("discipline");
			String safetyatwork = jsonObject.get("safetyatwork");
			String audit = jsonObject.get("audit");
			String workhandover = jsonObject.get("workhandover");
			String abnormal = jsonObject.get("abnormal");
			String composite = jsonObject.get("composite");
			String memo = jsonObject.get("memo");
			     
			Performance objPerformance = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", employee_id,month);
			if(objPerformance != null){
			    objPerformance.setDailywork(Double.parseDouble(dailywork));
			    objPerformance.setDiscipline(Double.parseDouble(discipline));
			    objPerformance.setSafetyatwork(Double.parseDouble(safetyatwork));
			    objPerformance.setAudit(Double.parseDouble(audit));
			    objPerformance.setWorkhandover(Double.parseDouble(workhandover));
			    objPerformance.setAbnormal(Double.parseDouble(abnormal));
			    objPerformance.setComposite(Double.parseDouble(composite));
			    objPerformance.setMemo(memo);
			    	 
			    performanceManager.save(objPerformance);
			   }else{
				   result = "1";
			   }
			     
		   }
		   return result;
	}
		 
		 
	
	
	
	public Map<String, Double> getWHBonus(String month, String dept_ID,String emp_id,String score,String bonus) {
		String user_id="sys";
	    user_id = SessionLocal.getUser().getUser_id();
	    Role role = userService.getRoleByUser(user_id);
	    if(role == null) return null;
        String role_name = role.getName();
		StringBuilder hql_average = new StringBuilder();
		StringBuilder hql_all = new StringBuilder();
		StringBuilder hql_actual = new StringBuilder();
		Map<String, Double> returnMap = new HashMap<String, Double>();
		hql_actual.append("select sum(p.bonus) as allbonus from Performance p left join PerformanceMon pm on pm.emp_id = p.emp_id and pm.month = p.month where p.isValid is true and p.factory = 'wh'");
		hql_average.append(" select sum(p.bonus)/count(*) as averagebonus from Performance p left join PerformanceMon pm on pm.emp_id = p.emp_id and pm.month = p.month where p.isValid is true and p.factory = 'wh'");
		hql_all.append(" from Performance p left join PerformanceMon pm on pm.emp_id = p.emp_id and pm.month = p.month where p.isValid is true and p.factory = 'wh'");
		
		
		 
	       
		
		
		if(StringUtil.isNotEmpty(month)){
			hql_average.append(" and p.month = '" + month + "' ");
			hql_all.append(" and p.month = '" + month + "' ");
			hql_actual.append(" and p.month = '" + month + "' ");
		}
		if(StringUtil.isNotEmpty(dept_ID)){
			if("10000380".equals(dept_ID) && "M1W".equals(role_name)){
				hql_average.append(" and pm.department_id = '" + dept_ID + "' ");
				hql_all.append(" and pm.department_id = '" + dept_ID + "' ");
				hql_actual.append(" and pm.department_id = '" + dept_ID + "' ");
			}else if("1000043410000379".equals(dept_ID) && ("FGW".equals(role_name) || "RMW".equals(role_name))){
			   hql_average.append(" and (pm.department_id = '10000379' or pm.department_id = '10000434') ");
	    	   hql_all.append(" and (pm.department_id = '10000379' or pm.department_id = '10000434') ");
	    	   hql_actual.append(" and (pm.department_id = '10000379' or pm.department_id = '10000434') ");
			}else{
				returnMap.put("averagebonus", 0.0);
		        returnMap.put("actualbonus", 0.0);
		        returnMap.put("allbonus", 0.0);
				
				return returnMap;
			}
		}else{
			if("M1W".equals(role_name)){
		    	   hql_average.append(" and pm.department_id = '10000380' ");
		    	   hql_all.append(" and pm.department_id = '10000380' ");
		    	   hql_actual.append(" and pm.department_id = '10000380' ");
		       }else{
		    	   hql_average.append(" and (pm.department_id = '10000379' or pm.department_id = '10000434') ");
		    	   hql_all.append(" and (pm.department_id = '10000379' or pm.department_id = '10000434') ");
		    	   hql_actual.append(" and (pm.department_id = '10000379' or pm.department_id = '10000434') ");
		       }
		}
			
		if(StringUtil.isNotEmpty(emp_id)){
			hql_average.append(" and p.emp_id = '" + emp_id + "' ");
			hql_all.append(" and p.emp_id = '" + emp_id + "' ");
			hql_actual.append(" and p.emp_id = '" + emp_id + "' ");
		}

		if(StringUtil.isNotEmpty(score)){
			hql_average.append(" and p.score = '" + score + "' ");
			hql_all.append(" and p.score = '" + score + "' ");
			hql_actual.append(" and p.score = '" + score + "' ");
		}

		if(StringUtil.isNotEmpty(bonus)){
			hql_average.append(" and p.bonus = '" + bonus + "' ");
			hql_all.append(" and p.bonus = '" + bonus + "' ");
			hql_actual.append(" and p.bonus = '" + bonus + "' ");
		}
		
		double averagebonus = hibernateInvoker.findUnique(Double.class, hql_average.toString())==null?0:hibernateInvoker.findUnique(Double.class, hql_average.toString());
		double actualbonus = hibernateInvoker.findUnique(Double.class, hql_actual.toString())==null?0:hibernateInvoker.findUnique(Double.class, hql_actual.toString());
		double allbonus = hibernateInvoker.find(Performance.class, hql_all.toString()).size() * 100.0;
		
		
		returnMap.put("averagebonus", averagebonus);
        returnMap.put("actualbonus", actualbonus);
        returnMap.put("allbonus", allbonus);
		
		return returnMap;
		
	}
	
	
	public List getwhgroups (int allFlag,int pno,String emp_id,String month,String dept_ID){
    	
        String user_id="sys";
        if(SessionLocal.getUser()!=null)
        user_id = SessionLocal.getUser().getUser_id();
        Role role = userService.getRoleByUser(user_id);
        if(role == null) return null;
        String role_name = role.getName();
        StringBuilder hql = new StringBuilder();

        
        
        hql.append(" select new map(pm.department_name as department_name,p.emp_id as emp_id,p.emp_name as emp_name,p.groups as group,c.label as label,c.id as id) ");
        hql.append(" from Performance p left join PerformanceMon pm on p.emp_id = pm.emp_id and p.month = pm.month");
        hql.append(" left join Code c on c.id = p.groups where p.isValid = true and pm.isValid = true ");
     
       
           if("RMW".equals(role_name)){
	    	   hql.append(" and pm.department_id = '10000434' ");
	       }else if("M1W".equals(role_name)){
	    	   hql.append(" and pm.department_id = '10000380' ");
	       }else if("FGW".equals(role_name)){
	    	   hql.append(" and pm.department_id = '10000379' ");
	       }
        
        
        if(StringUtil.isNotEmpty(emp_id)){
        	hql.append(" and p.emp_id = '" + emp_id + "' ");
        }
        
		if(StringUtil.isNotEmpty(month)){
			hql.append(" and p.month = '" + month + "' ");	
		}
		
		if(StringUtil.isNotEmpty(dept_ID)){
			if("10000380".equals(dept_ID)){
				hql.append(" and pm.department_id = '" + dept_ID + "' ");
			}else{
				hql.append(" and (pm.department_id = '10000434' or pm.department_id = '10000379') ");
			}
			
		}
		
		
		 hql.append(" order by p.groups desc,p.emp_id desc");
        
        List list = hibernateInvoker.find(Map.class, pno,10, hql.toString());
        List alllist = hibernateInvoker.find(Map.class, hql.toString());
    	List<Map<String, Object>> listTemp = new ArrayList<Map<String, Object>>();
    	Map<String, Object> map = new HashMap<String, Object>();
    	map.put("allTotal", alllist.size());
    	if(allFlag==1)map.put("groups", alllist);
    	else map.put("groups", list);
    	listTemp.add(map);
		return listTemp;
    } 
 
	
	
	
	
	
	@Transactional
	public String savewhgroups(List<Map<String, String>> list) {
			String result = "0";
			 
		 for(int i = 0; i < list.size(); i++) {
			Map<String, String> jsonObject = list.get(i);
		    String month = jsonObject.get("month");
		    String employee_id = jsonObject.get("employee_id");
		    String employee_name = jsonObject.get("employee_name");
			String group = jsonObject.get("group");
			     
			Performance objPerformance = performanceManager.findUnique("from Performance p where p.isValid is true and p.emp_id = ?0 and p.month=?1", employee_id,month);
			if(objPerformance != null){
				Code c = hibernateInvoker.findUnique(Code.class,"from Code c where c.name = '" + group + "'");
			    if(c != null){
			    	objPerformance.setGroups(c.getId());
				    performanceManager.save(objPerformance);
			    }else{
			    	 result = "1";
			    }
			   }else{
				   result = "1";
			   }
			     
		   }
		   return result;
	}
	
	
	
	
		 
		 
}
